0

I'm trying to write a SQL query to return the first value based on an order by for a date column where duplicates are returned but I am getting one returned row overall (I'm trying to use Select Top 1, just incorrectly) Here's the query just to get all rows, duplicates included:

SELECT u.userid, Table1.Col1, Table1.Col2, Table1.Col3
FROM Table1
JOIN Table2 ON Table2.PK = Table1.FK
JOIN [User] u ON Table2.UserId = u.UserId
WHERE u.UserId IN (2,4,6,7,10,11,12,8,5)
ORDER BY Table1.Date DESC

Each User is associated with multiple records in Table1, but I want just the first returned Table1 columns for a User based on the most recent Table1.Date value. I'm assuming I need a subquery, I just don't know to properly construct it and any help is appreciated.

The above query shows current data (top 4 shown below for ease):

userid col 1 col 2 col 3
1 3 2021 null
1 4 2022 null
2 5 2021 null
2 6 2022 null

I would like to return only the 2nd and 4th rows as those are the first column values associated with the users (users 1 and 2 here) and have the greatest date (in col 2, from the ORDER BY at the end of the above query).

jmath412
  • 419
  • 3
  • 13

1 Answers1

3

You can use top(1) if you refactor your query to use apply()

Something like the following:

select u.userid, t.*
from [User] u
cross apply (
  select top(1) t1.col1, t1.col2, t1.col3
  from table2 t2
  join table1 t1 on t1.fk = t2.pk
  where t2.userid = u.userid
  order by t1.date desc
)t
where u.UserId in (2,4,6,7,10,11,12,8,5);
Stu
  • 30,392
  • 6
  • 14
  • 33
  • That did it, thank you so much! I've never heard of cross apply, so I learned another new item as well :) – jmath412 May 18 '23 at 19:10
  • 1
    This is SQL Server's implementation of a *lateral join*, it's been in the product since 2005 but often overlooked! – Stu May 18 '23 at 19:12