SQL Server Performance

Select 20 records Only

Discussion in 'SQL Server 2005 General Developer Questions' started by sqlderby, Oct 11, 2011.

  1. sqlderby Member

    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...
  2. FrankKalis Moderator

    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.
  3. sqlderby Member

    Can we do it without using CROSS APPLY ...e.g simple T-SQ L.
  4. satya Moderator

    CROSS APPLY itself is the SIMPLE TSQL, what is the problem in using this join condition?
  5. FrankKalis Moderator

    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;

Share This Page