Select 20 records Only | SQL Server Performance Forums
SQL Server Performance Forum – Threads Archive
Select 20 records OnlyHi All I am trying to select only 20 records of each agent from a table. There could be any number of records for each agent in the table. These records could be recent ones or random ones… Pls help…
Something like this should work:
SELECT * FROM T1 CROSS APPLY (SELECT TOP (20) * FROM T2 WHERE T1.keycol = T2.keycol ORDER BY somecolumn) a
Can we do it without using CROSS APPLY …e.g simple T-SQ L.
CROSS APPLY itself is the SIMPLE TSQL, what is the problem in using this join condition?
If memory serves me correctly, you can use something like
SELECT t1.CustomerID, t1.OrderDate FROM Orders t1 WHERE t1.OrderDate IN (SELECT TOP 20 t2.OrderDate FROM Orders t2 WHERE t2.CustomerID = t1.CustomerID ORDER BY t2.OrderDate DESC) ORDER BY t1.CustomerID, t1.OrderDate DESC;