SQL Selecting max date by year for 3 unique IDs

user_7210660 0
Viewed 53

I've got a list like this where I need to get the max date per year for each combination of unique IDs.

Calculated-Date|ID_A | ID_B | ID_C
   2017/11/5   |  AB |  A   |  C
   2017/10/5   |  AB |  A   |  C
   2016/10/5   |  AB |  A   |  C
   2017/10/5   |  AB |  C   |  C
   2016/10/5   |  AB |  C   |  C
   2016/10/30  |  AB |  C   |  C
   2015/10/5   |  AB |  C   |  C

What I would like to get is this

    Calculated-Date|ID_A | ID_B | ID_C
       2017/11/5   |  AB |  A   |  C
       2016/10/5   |  AB |  A   |  C
       2017/10/5   |  AB |  C   |  C
       2016/10/30  |  AB |  C   |  C
       2015/10/5   |  AB |  C   |  C

I've tried a variation of subqueries but I am fairly new to SQL and is truly stuck at the moment. Any help is much appreciated!

I've tried this:

select distinct max(Calculated-Date), ID_A, ID_B, ID_C
    from TABLE_1
    group by Calculated-Date, ID_A, ID_B, ID_C
order by ID_A, ID_B, ID_C
sql-server sql
3 Answers

You can use GROUP BY . . . with some date arithmetic:

select id_a, id_b, id_c, max(calculated_date)
from t
group by id_a, id_b, id_c, year(calculated_date);

Note: This assumes that the date is, indeed, stored using a date/time date type.

user_11440350

You Can Try

SELECT * FROM TableName ORDER BY Calculated-Date DESC
user_69190220 
Select * from
(
   Select id_a, id_b, id_c, calculateddate, 
          row_number() over ( partition by id_a,id_b,id_c,
              year(calculateddate) order by calculateddate desc) r
From table 
) x
Where r=1
user_50097860
Related