RE: Limiting SQL results returned

Looks like ROW_NUMBER would work. Try this in Run Sql Scripts if you're on
V5R4:

SELECT * FROM
(SELECT A.*,
ROW_NUMBER() OVER(PARTITION BY status ORDER BY status) AS RN
FROM myfile A) B
WHERE RN IN (1,2,3,4,5)

or using common table expression:

WITH CTE AS
(SELECT A.*,
ROW_NUMBER() OVER(PARTITION BY status ORDER BY status) AS RN
FROM myfile A)
SELECT * FROM CTE WHERE RN IN (1,2,3,4,5)

NOTE: It errored out on my system, but we're way behind on db fixpacks.

Elvis

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

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

Scratch that syntax, I just realized it won't work. Try this instead

WITH CTE1 AS
(SELECT * FROM MYFILE WHERE STATUS = 'status 1' FETCH FIRST 5 ROWS ONLY),
CTE2 AS
(SELECT * FROM MYFILE WHERE STATUS = 'status 2' FETCH FIRST 5 ROWS ONLY),
CTE3 AS
(SELECT * FROM MYFILE WHERE STATUS = 'status 1' FETCH FIRST 5 ROWS ONLY),
......
SELECT * FROM CTE1
UNION ALL
SELECT * FROM CTE2
UNION ALL
SELECT * FROM CTE3
....

Ugly, I know.

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

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

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

-----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