Re: Limiting SQL results returned -- MIDRANGE-L

This list is great!

Elvis

Thanks for pointing me in the right direction.

After doing it the hard way, this worked for me;

With T1 AS (SELECT Status,
InfoField,
ROW_NUMBER() over (PARTITION BY Status ORDER BY Status) as Nbr
from FileName ORDER BY Status )

SELECT * FROM T1 WHERE Nbr < 6

Any thoughts on how to get a random 5 selected instead of the first 5?

Thanks again.

Jim

On 7/31/07, Elvis Budimlic <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.