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...
    sqlderby, Oct 11, 2011
    #1

  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.
    FrankKalis, Oct 11, 2011
    #2

  3. sqlderby Member

    Can we do it without using CROSS APPLY ...e.g simple T-SQ L.
    sqlderby, Oct 18, 2011
    #3

  4. satya Moderator

    CROSS APPLY itself is the SIMPLE TSQL, what is the problem in using this join condition?
    satya, Oct 21, 2011
    #4

  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;
    FrankKalis, Oct 21, 2011
    #5

Share This Page