MS SQL: How to get the newest date in a table with several equal keys
- by Qohelet
Unfortunately my knowledge related to statements like "group by" and "having" is quite limited, so hopefully you can help me:
I have a view -here's an excerpt- (if we have some Europeans here - it's v021 of Winline/Mesonic):
ID | Artikelbezeichnung1 | Bez2 | mesoyear
_____________________________________________________________________
1401MA70 | Marga ,Saracena grigio,1S,33,3/33,3 | Marazzi | 1344
1401MA70 | Marga ,Saracena grigio,1S,33,3/33,3 | Marazzi | 1356
1401MA70 | Marga ,Saracena grigio,1S,33,3/33,3 | Marazzi | 1356
1401MA71 | Marga ,Saracena beige,1S,33,3/33,3 | Marazzi | 1344
1401MA71 | Marga ,Saracena beige,1S,33,3/33,3 | Marazzi | 1356
1401MA71 | Marga ,Saracena beige,1S,33,3/33,3 | Marazzi | 1356
2401CR13 | Crista,Mahon rojo,1S,33,3/33,3 | Cristacer | 1332
2401CR13 | Crista,Mahon rojo,1S,33,3/33,3 | Cristacer | 1344
So the ID is not unique and I just need the one with the highest val in "mesoyear".
My fist solution was:
Select c015 as ID,
c003 as Artikelbezeichnung1,
c074 as Bez2,
mesoyear
from CWLDATEN_91.dbo.v021
group by c015
having mesoyear = max(mesoyear)
But this doesn't work at all...
Msg 8121, Level 16, State 1, Line 8
Column 'CWLDATEN_91.dbo.v021.mesoyear' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
So I just removed the "having" statement and it went "better":
Msg 8120, Level 16, State 1, Line 2
Column 'CWLDATEN_91.dbo.v021.c003' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
So I tried to remove the error just by adding things to the "group by". And it worked.
Select c015 as ID,
c003 as Artikelbezeichnung1,
c074 as Bez2,
max(mesoyear)
from CWLDATEN_91.dbo.v021
group by c015,c003,c074
gives me exactly what I want.
But the correct Select contains about 24 columns and some calculations as well. The problem can't be solved just by adding all the columns to the "group by"...?
Can someone please help me to find a proper command?
Thank you!
