×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
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
midrange.com
