CODEWITHSUNDEEP
sql-server
Nightmaresux
Nightmaresux

Reputation: 538

Select Values with lowest date

i have table Rates

Key   Rate      Dt
1      2      201401
1      2      201402
1      3      201403
2      1      201308
2      2      201309 
2      1      201301

and i want to select the Key and rate that has the lowest Dt.

so the result should look like : 

   Key   Rate      Dt
    1      2      201401
    2      1      201308

I tried this :

select Key,Rate,Dt from Rates where Dt = ( select MIN(dt) from Rates group by Key)

but its not selecting the lowest date only for the specific key... Can you help me please? Thank you

Upvotes: 0

Views: 176

Answers (3)

TT.
TT.

Reputation: 16137

I think you meant this:

  SELECT
    yt.key,
    yt.rate,
    yt.dt
  FROM
    (
      SELECT
        key,
        MIN(dt) AS min_dt
      FROM
        rates
      GROUP BY
        key
     ) AS kmd
     INNER JOIN rates AS yt ON
       yt.key=kmd.key AND
       yt.dt=kmd.min_dt

Upvotes: 0

Christian Phillips
Christian Phillips

Reputation: 18749

You can use a CTE to get the relevant records...

;
WITH    cteLowestDate
          AS ( SELECT   [Key]
                       ,Rate
                       ,Dt
                       ,ROW_NUMBER() OVER (PARTITION BY [Key] ORDER BY Dt) AS 'rownum'
               FROM     Rates
             )
    SELECT  [Key], Rate, Dt
    FROM    cteLowestDate
    WHERE   rownum = 1

I have added a SQL Fiddle

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93694

SELECT KEY,
       Rate,
       Dt
FROM   (SELECT KEY,
               Min(dt) dt
        FROM   Rates
        GROUP  BY KEY) A
       JOIN Rates B
         ON A.KEY = B.KEY
            AND A.dt = B.dt

Upvotes: 1

Related Questions