Select Query on Sql Server

Question

The question may be very simple but i don't know how to fix it,

I have this table structure

sno  left Right
1    2      1
2    2      2
3    1      2
4    3      1 
5    2      4
6    7      1    
7    2      8

How do I get a result set like the one below

sno  left   Right Result
1    2      1     1
2    2      2     2
3    1      2     1
4    3      1     1
5    2      4     2
6    7      1     1
7    2      8     2

I wanna select the Data what mimimum value is matched between two columns,

Eg:3 and 1

1 is minimum value between these two and 1 is matched with 3, so the matched value is 1.

eg: 2 and 4

2 is minimum value between these two and 2 is is mathed with 4, so the matched value is 2.

Edited:

If choose 8 and 2 for example

8 contains(1,2,3,4,5,6,7,8)

2 contains(1,2)

So the Result is 2

Because 2 values are matched here. I hope i explained it well, thanks

nope, Im not calculating the diff between those two columns @jarlh — King_Fisher
– King_Fisher, Commented May 18, 2015 at 11:35
That's what @jarlh is doing, calculating the difference: left - right — Christian Barron
– Christian Barron, Commented May 18, 2015 at 11:38
So, are you saying that if left is smaller then 1, if right is smaller then 2, if both are equal then 0? — splattne
– splattne, Commented May 18, 2015 at 11:40
Ok, now we know what you're not calculating. Could you please explain what you're calculating? — dnoeth
– dnoeth, Commented May 18, 2015 at 11:59
@King_Fisher, this is just a minimum. Why are you overcomplicationg this? — Giorgi Nakeuri
– Giorgi Nakeuri, Commented May 18, 2015 at 12:25

Donal · Accepted Answer · 2015-05-18 11:36:06Z

2

The following SQL will return the positive value of a subtraction operation between the left and right values - in a column with Result as the header. It will calculate the difference between left and right values - ABS will make the result positive.

SELECT
    sno,
    left,
    Right,
    ABS(left - right) AS Result
FROM tablename

answered May 18, 2015 at 11:36

Donal

33.2k10 gold badges69 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

King_Fisher Over a year ago

Im NOT Calculating the Difference .:)

King_Fisher Over a year ago

I have updated my Result set again. Please check it,

Giorgi Nakeuri · Accepted Answer · 2015-05-18 12:30:41Z

1

One of the possible solutions:

DECLARE @t TABLE ( sno INT, l INT, r INT )

INSERT  INTO @t
VALUES  ( 1, 2, 1 ),
        ( 2, 2, 2 ),
        ( 3, 1, 2 ),
        ( 4, 3, 1 ),
        ( 5, 2, 4 ),
        ( 6, 7, 1 ),
        ( 7, 2, 8 )


SELECT *,
    (SELECT MIN(v) FROM (VALUES(l),(r)) m(v)) AS m
FROM @t

Output:

sno l   r   m
1   2   1   1
2   2   2   2
3   1   2   1
4   3   1   1
5   2   4   2
6   7   1   1
7   2   8   2

answered May 18, 2015 at 12:30

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

4 Comments

King_Fisher Over a year ago

Thank you very much, but i didn't get your query, you have used 'v' but you didn't declared that, what kind of query is this.

Giorgi Nakeuri Over a year ago

(VALUES(l),(r)) this is table expression with 1 column and 2 rows l and r then you call it m(v) i.e. table name is m and column name is v.

King_Fisher Over a year ago

Can you refer me any article related your query

Giorgi Nakeuri Over a year ago

@King_Fisher, beyondrelational.com/modules/2/blogs/70/posts/10905/…

dnoeth · Accepted Answer · 2015-05-18 12:13:57Z

1

case 
     when left < right then left 
     else right 
end

edited May 18, 2015 at 12:13

answered May 18, 2015 at 12:05

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

6 Comments

King_Fisher Over a year ago

Sir, I have Update with example .

dnoeth Over a year ago

@King_Fisher: Your example simply returns the lower of both values, which is exactly what this CASE is doing.

dnoeth Over a year ago

Based on your modified examples it's even simpler... Btw, most DBMSes support a function like LEAST for this kind of logic.

King_Fisher Over a year ago

But,whats the condition if both values are same?

King_Fisher Over a year ago

Im using Sql server 2008 , It doesn't have that in-built function .

|

Collectives™ on Stack Overflow

Select Query on Sql Server

3 Answers 3

2 Comments

4 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

4 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related