CODEWITHSUNDEEP
sql
DarkW1nter
DarkW1nter

Reputation: 2861

limit number of records returned in sql query

if "select * from table;" gives the results below, how can I limit it to only show the 1st 3 records for each ref?

thanks

ref other field
----------------------
1234 a
1234 b
1234 c
1234 d
1234 e
5678 a
5678 b
5678 c
5678 d

Upvotes: 1

Views: 935

Answers (3)

Thomas
Thomas

Reputation: 64674

Another solution that will work in SQL Server 2005+

Select ...
From MyTable As T
    Cross Apply (
                Select TOP 3 T2.OtherField
                From MyTable As T2
                Where T2.ref = T.ref
                Order By T2.OtherField
                ) As Z

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135918

For Microsoft SQL Server 2005+:

SELECT ref, OtherField
    FROM (SELECT ref, OtherField, 
                 ROW_NUMBER() OVER(PARTITION BY ref ORDER BY OtherField) AS RowNum
              FROM YourTable) t
    WHERE t.RowNum <= 3

The same query, using a CTE:

WITH cteRowNum AS (
    SELECT ref, OtherField, 
           ROW_NUMBER() OVER(PARTITION BY ref ORDER BY OtherField) AS RowNum
        FROM YourTable
)
SELECT ref, OtherField
    FROM cteRowNum
    WHERE RowNum <= 3

Upvotes: 2

Benoit
Benoit

Reputation: 79233

With Oracle:

WITH subquery as 
(
  SELECT ref, row_number() over (partition by ref) rank, other_field
    FROM my_table
)
SELECT ref, other_field from subquery where rank <= 3;

Lookup for Oracle analytic functions.

Upvotes: 1

Related Questions