CODEWITHSUNDEEP
sql-server
damini
damini

Reputation: 1

Select only maximum value record for multiple rows

I have one table, which having records like

Unit    Rate    Date-effected
-------------------------------------------
ALHA    ILS     2014-03-02 00:00:00.000
ALHA    ILS     2014-08-02 00:00:00.000
BUCK    ILS     2013-02-14 00:00:00.000
BUCK    ILS     2014-03-02 00:00:00.000
BUCK    ILS     2014-08-02 00:00:00.000
CASC    ILD     2013-02-14 00:00:00.000
CASC    ILD     2014-03-02 00:00:00.000
CASC    ILD     2014-08-02 00:00:00.000

now, I want only maximum date value record selection in result table. which is,

Unit    Rate    DateEffected
-------------------------------------------
ALHA    ILS     2014-08-02 00:00:00.000
BUCK    ILS     2014-08-02 00:00:00.000
CASC    ILD     2014-08-02 00:00:00.000

Upvotes: 0

Views: 62

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use ROW_NUMBER for this:

SELECT Unit, Rate, DateEffected
FROM (
  SELECT Unit, Rate, DateEffected,
         ROW_NUMBER() OVER (PARTITION BY Unit 
                            ORDER BY DateEffected DESC) AS rn
FROM mytable) AS t
WHERE t.rn = 1

Upvotes: 2

Sandesh
Sandesh

Reputation: 1044

Hope this helps.

SELECT
 Unit,
 Rate,
MAX(DateEffected) AS MaxDateEffected
FROM TableName
GROUP BY  Unit,Rate

Upvotes: 2

Related Questions