Top ten calls by user

[tahoe3]

I have a telephone call table and I need to return the 10 longest calls by user last name. Fields used would be lastname and callduration. Callduration you be sorted descending.



Thanks

 

[r937]

select lastname
     , callduration
  from calltable as T
 where ( select count(*) 
           from calltable  
          where callduration 
            > T.callduration ) < 10

r937.com | rudy.ca

 

[ddiamond]

select top 10 lastname, callduration
from calltable
order by callduration desc

 

[PHV]

Another way:
SELECT lastname, callduration
FROM calltable AS A
WHERE callduration In (SELECT TOP 10 callduration
FROM calltable WHERE lastname=A.lastname ORDER BY 1 DESC)
ORDER BY 1, 2 DESC

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r937]

caution: mine is with ties, phv's isn't ;-)

r937.com | rudy.ca