limit number of records returned in sql query

if "select * from ta开发者_StackOverflow社区ble;" 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 

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

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.

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