August 26, 2009 at 1:35 pm
Say i have following three tables
License:
Id, SerialNum
1, x123
2, x234
Product:
Id, Description, ReleaseDate
1, msn v1, 01-01-09
2, Os v1, 01-01-09
3, msn v2, 06-01-09
License_Product:
Lic_Id, P_Id
1, 1
1, 2
2, 2
1, 3
What will be the query to get the most recent row from Product table for each product for each license based on release date. e.g for license id 1 i need following data.
LicId, SerialNum, PId, ProdDesc, ReleaseDate
1, x123, 3, msn v2, 06-01-09
1, x123, 2, Os v1, 01-01-09
August 26, 2009 at 2:05 pm
Should post create table and insert data statements with your questions, though I guess in this case it's not really too necessary. Anyway, try this:
WITH CTE AS
(
SELECT Lic_ID, SerialNum, P_ID, [Description], ReleaseDate, ROW_NUMBER OVER (PARTITION BY Lic_ID ORDER BY ReleaseDate DESC) AS RowNum
FROM License l
JOIN License_Product lp ON l.ID = Lic_ID
JOIN Product p ON p.ID = P_ID
)
SELECT Lic_ID, SerialNum, P_ID, [Description], ReleaseDate
FROM CTE
WHERE RowNum = 1
ROW_NUMBER is a function which basically provides you with a number you can use to differentiate records, through PARTITION BY and ORDER BY.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply