RE: Limiting SQL results returned

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Brute force it by running something like:

SELECT * FROM MYFILE WHERE STATUS = 'status 1' FETCH FIRST 5 ROWS ONLY
UNION ALL
SELECT * FROM MYFILE WHERE STATUS = 'status 2' FETCH FIRST 5 ROWS ONLY
UNION ALL
SELECT * FROM MYFILE WHERE STATUS = 'status 1' FETCH FIRST 5 ROWS ONLY
......

and do it as many times as you have unique statuses in your table (you said
about 20 or so).

I haven't used new RANK, PARTITION and recursive CTEs yet so I'm not sure if
there is more elegant way to address this.
I'd be curious to see one as well.

Elvis

Celebrating 10-Years of SQL Performance Excellence
http://centerfieldtechnology.com/training.asp

-----Original Message-----
Subject: Limiting SQL results returned

SQL Guru's

Is there a way to limit the number of rows returned from a SQL statement?
For instance, I want a sampling of 5 records for each status from a file.

Status, Fn1, .... Fn6

For _each_ status that appears in the file, I want 5 and only 5 resulting
rows, could be the first 5 or last 5 or a random 5 - although a random 5
would be awesome - would help with another project I have ahead of me.
There could be 20 different status codes in the file, and I want 5 records
for all 20 status codes.   I guess that there might not be 5 for some
statuses, so getting up to 5 results would be OK too.

Any thoughts?

Jim