SQL subsetting after a first subsetting-CodePudding

I have a table called Mytable like this:

Code	Date	Name1	Name 2	Price
ABC123	2022-03-14	Myname1	Myname2	4
ABC123	2022-03-14	Myname1	Myname2	5
ABC123	2022-03-13	Myname1	Myname2	4
ABC123	2022-03-13	Myname1	Myname2	5
ABC456	2021-12-31	Myname1	Myname2	4
ABC456	2021-12-31	Myname1	Myname2	5
ABC456	2021-03-13	Myname1	Myname2	4
ABC456	2021-03-13	Myname1	Myname2	5
DEF123	2022-03-13	OtherName1	OtherName2	4
DEF123	2022-03-14	OtherName1	OtherName2	5

I want to get only this rows:

Code	Date	Name1	Name 2	Price
ABC123	2022-03-14	Myname1	Myname2	4
ABC123	2022-03-14	Myname1	Myname2	5
ABC456	2021-12-31	Myname1	Myname2	4
ABC456	2021-12-31	Myname1	Myname2	5

That is, I want to subset the rows with Name1=Myname1 and Name2= Myname2:

SELECT * FROM Mytable WHERE Name1='Myname1' AND Name2='Myname2'

But I don't want all of them. I need only the rows with the most recent dates for each Code. Each code has a different most recent date. I think that I can get the most recent date for each code with:

SELECT Code, MAX(Date) FROM Mytable WHERE Name1='Myname1' AND Name2='Myname2' GROUP BY Code

But I don't know how to combine this two subsettings in one query, since adding the rest of the column names to the last query doesn't work:

SELECT Name1, Name2, Code, MAX(Date) FROM Mytable WHERE Name1='Myname1' AND Name2='Myname2' GROUP BY Code

Thanks!!

CodePudding user response：

SELECT Code, [Date], Name1, Name2, Price
FROM (
    SELECT *, dense_rank() over (partition by code order by code, [date] desc) rn
    FROM [Mytable]
    WHERE Name1='Myname1' AND Name2='Myname2'
) T 
WHERE rn = 1

See it work here:

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=66c6ec98a906a8a49ac5e55e1d1a6ac2

CodePudding user response：

Try adding Name1 & Name2 to group by

SELECT 
  Name1, 
  Name2, 
  Code, 
  MAX(Date) AS "Date",
  Price
FROM Mytable 
WHERE Name1='Myname1' 
AND Name2='Myname2' 
GROUP BY Code, Name1, Name2

CodePudding user response：

I never really use partition (out of ignorance) so I'd probably just write this. The optimizer should make this run fast enough and it's super easy to understand.

SELECT Code, Date, Name1, Name2, Price
FROM Mytable t1
WHERE Date IN (
  SELECT MAX(Date)
  FROM Mytable t2
  WHERE t1.Code = t2.Code
)
AND Name1 = 'Myname1'
AND Name2 = 'Myname2'