show second result for multiple records - MSSQL2005

Question

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

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

thanks

Answer 1

You need to use row_number() function, try something like this:

select
    link_to_client, call_ref 
from
    (
        select
            link_to_client, call_ref, 
            row_number() over (partition by link_to_client order by call_ref desc) n
        from 
            calls with (nolock) 
        where 
            link_to_client in ('G/1931') 
            and call_type = 'PM' 
    ) x
where
    n = 2 -- second result for every client