SergioKastro SergioKastro - 7 months ago 5
SQL Question

I need to select the Max id from a table returning all the rows

I hope to get some help and also I can explain myself clear enough about what I need to do.

I have the following query:

SELECT
id.ItemId, id.DocId AS LastVersion,
MONTH(d.docUploadDt) as MontUploaded,
YEAR(d.docUploadDt) as YearUploaded
FROM
dbo.ItemDocument id
INNER JOIN
dbo.Documents d on id.docId = d.docId
GROUP BY
ItemId, id.DocId, d.docUploadDt


This will return data like this (this is an example for one particular itemId):

ItemId LastVersion MontUploaded YearUploaded
1728903 1544371 2 2016
1728903 1544372 2 2016
1728903 1544373 3 2016
1728903 1544374 4 2016
1728903 1544375 4 2016
1728903 1544376 4 2016


And my purpose it is to retrieve the last Version of a document uploaded each month. I would like to get the following result

ItemId LastVersion MontUploaded YearUploaded
1728903 1544372 2 2016
1728903 1544373 3 2016
1728903 1544376 4 2016


I cannot figured out how to use MAX statement. Could anyone help me out?

Thanks in advance,

Sergio

vkp vkp
Answer

You have to group by month and year of uploaddate,itemid and select the max docid.

SELECT id.ItemId, MAX(id.DocId) AS LastVersion,
MONTH(d.docUploadDt) as MontUploaded, YEAR(d.docUploadDt) as YearUploaded
FROM dbo.ItemDocument id 
INNER JOIN dbo.Documents d on id.docId = d.docId
GROUP BY id.ItemId,YEAR(d.docUploadDt),MONTH(d.docUploadDt)