any SQL gurus?

Discussion in 'Programming' started by Chenks, Aug 29, 2013.

Share This Page

  1. Chenks

    Chenks Registered Trader

    Joined:
    Jan 5, 2002
    Messages:
    3,741
    Likes Received:
    0
    I've got a query that displays the second result for one customer. what i now need to do is show the second result for each customer in a particular list (for example 20 different customers).

    how would i do this? MS SQL2000 via SSMS 2005

    current query for 1 customer is

    Code:
    SELECT TOP 1 link_to_client, call_ref
    FROM
    (
        SELECT TOP 2 link_to_client, call_ref
        FROM calls WITH (NOLOCK)
        WHERE link_to_client IN ('G/1931')
            AND call_type = 'PM'
        ORDER BY call_ref DESC
    ) x
    ORDER BY call_ref
     
  2. Samsonite

    Samsonite Mine's a frosty one

    Joined:
    Oct 28, 2004
    Messages:
    1,025
    Likes Received:
    0
    There are probably a few ways to do this, but firstly we would need to know a bit more about the data involved.

    Is the only link to client in the format of G/1931? Is there no numeric ID?

    What is call_ref? What data is in there? That would effect how efficient you could make the query.
     
  3. Chenks

    Chenks Registered Trader

    Joined:
    Jan 5, 2002
    Messages:
    3,741
    Likes Received:
    0
    i got it sorted in the end.

    exclude the maximum call_ref for each link_to_client, then take the maximum of that:

    Code:
    SELECT  calls.link_to_client, 
            MAX(calls.Call_Ref) call_ref
    FROM    calls WITH (NOLOCK)
            LEFT JOIN 
            (   SELECT  link_to_client, MAX(Call_Ref) call_ref
                FROM    calls WITH (NOLOCK)
                WHERE   calls.call_type = 'PM'
                AND      calls.link_to_client IN (blah, blah)
                GROUP BY link_to_client
            ) MaxCalls
                ON MaxCalls.link_to_client = calls.link_to_client
                AND MaxCalls.Call_ref = calls.call_ref
    WHERE   calls.call_type = 'PM'
    AND     MaxCalls.link_to_Client IS NULL
    AND      calls.link_to_client IN (blah, blah)
    GROUP BY calls.link_to_Client;