Comparision

[gsavitha]

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

 

[MarkSweetland]

select CStid, min(Price) as MinPrice
from [YourTable] a
group by CStid


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[Rhys666]

Have you looked at the RANK function?

WITH RankedPrice
(
    CSTid
    ,Price
    ,Ranking
)
AS
(
    SELECT
        CSTid
        ,Price
        ,RANK() OVER
        (
            PARTITION BY
                CSTid
            ORDER BY
                Price ASC
        )
)
SELECT
    CSTid
    ,Price
FROM
    RankedPrice
WHERE
    Ranking = 1;

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett