0

What I want to do is, first select the latest date of each product and then include the id number of each product. If there are more than one product on the same latest date, choosing the largest id number.

Sample Table(product):

ID     Name     Date        ..    ..  ..   
312    ABC     4/3/2015     
289    ABC     4/3/2017    
156    ABC     4/3/2016     
631    XYZ     4/12/2015    
887    XYZ     4/3/2009    
125    XYZ     4/3/2009

Expected Result:

ID     Name     Date        ..    ..  ..   
289    ABC     4/3/2017         
631    XYZ     4/12/2015

What I have done, choosing the largest date and maximum id separately:

SELECT MAX(ID) AS Max_ID, Name, Max(Date) AS Latest_Date
From product
GROUP BY Name

ID     Name     Date        ..    ..  ..   
312    ABC     4/3/2017          
887    XYZ     4/12/2015

How can I get the expected result by following the requirement? Thank you.

UnicornKKYY
  • 75
  • 6
  • use [row_number() over (partition by ... order by ...)](https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver15) – Squirrel Jun 18 '21 at 09:28

1 Answers1

0

Try this

SELECT P1.ID, P1.NAME, P1.DATE 
FROM PRODUCT P1 INNER JOIN (
SELECT NAME, MAX(DATE)DATE FROM PRODUCT
GROUP BY NAME) P2 ON (P1.NAME = P2.Name AND P1.DATE = P2.DATE) ORDER BY P1.ID

Let me know in case you face any issue.

Amit Verma
  • 2,450
  • 2
  • 8
  • 21