Problem to get partial records

[RaulMA]

Sample case, the real one contaiins thousands of records with multiple companies and I just want the top 5 or N number of recors.

COMPANY USER
COMP_A gbuske
COMP_A gcaldog
COMP_A kdubell
COMP_A lboldiz
COMP_A rgronb
COMP_A rbal
COMP_A abnuk
COMP_B jwald
COMP_B halab
COMP_B abnuk1
COMP_B abnuk2
COMP_B abnuk3
COMP_B abnuksal
COMP_B abnukall
COMP_B abnuktrd

Looking to get the 5 top records for each company the expected result should be:

COMPANY USER
COMP_A gbuske
COMP_A gcaldog
COMP_A kdubell
COMP_A lboldiz
COMP_A rgronb
COMP_B jwald
COMP_B halab
COMP_B abnuk1
COMP_B abnuk2
COMP_B abnuk3

Thanks

 

[ESquared]

It's kind of nasty in SQL Server 2000, easier in 2005.

SELECT
   Company,
   UserName,
   OrderByColumn
FROM
   MyTable T
WHERE
   OrderByColumn <= (SELECT Max(OrderByColumn) FROM (SELECT TOP 5 OrderByColumn FROM MyTable WHERE Company = T.Company ORDER BY OrderByColumn ASC) X)
ORDER BY
   Company,
   OrderByColumn

 

[ESquared]

better:

DECLARE @N int
SET @N = 2

SELECT
   Company,
   UserName,
   OrderByColumn
FROM
   MyTable T
WHERE
   @N >= (
      SELECT Count(*) FROM MyTable T2 WHERE T2.Company = T.Company AND T2.OrderByColumn <= T.OrderByColumn
   )
ORDER BY
   Company,
   OrderByColumn