Comparision - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Comparision

Author

Topic

satheesh
Posting Yak Master

152 Posts

Posted - 2011-07-20 : 08:17:45

Dear All,

I have a to select all rows from cst table.In this table we have two cstid for each customer

But the corresponding 'price' field shows different price.I want to select all the rows, which has less price than another one.

For ex:

CStid Price

1000 14.98
1000 7.53
2000 83.57
2000 176.87
3000 23.07
3000 3.67

Result be
1000 7.53
2000 83.57
3000 3.67

How to do this.ie.Need to compare adjacent rows and pick the one with low value.Any help reg this highly appreciated.Many Thanks.

Regards,
SG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-20 : 08:24:17

[code]
SELECT CStid,Price
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY CStid ORDER BY Price) AS rn,*
FROM table
)t
WHERE rn=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-07-20 : 11:05:03

quote:
How to do this.ie.Need to compare adjacent rows and pick the one with low value.Any help reg this highly appreciated.Many Thanks.

What do you mean by adjacent rows? You realize that tables (sets) do not have an order. Is there another column that you can use to order the set?

If the previous ROW_NUMBER solution works for you, then here is another way:

SELECT CStid, MIN(Price) AS Price
FROM cst
GROUP BY CStid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources