Select 20 records Only | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Select 20 records Only

Hi 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:
Code:
SELECT
    *
FROM
    T1
    CROSS APPLY (SELECT TOP (20) * FROM T2 WHERE T1.keycol = T2.keycol ORDER BY somecolumn) a
If you remove the ORDER BY you get some kind of "randomness" in the resultset.
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
Code:
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;

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |