Limiting # of records returned 2

[wood]

I am needing to limit the number of records that are returned by my SQL Statement.

The statement can return more than 300 records or sometimes none. Whether there are 3 records returned or 300 returned, I just want the first five(that is if there is even five to return).

Does anyone have an idea?

 

[wushutwist]

Use TOP

TOP 5
SELECT * 
FROM CUSTOMERS;

Wushutwist

 

[wood]

I tried that, didn't work.

I have also tried:

set rowcount 5

and

limit to 5

Any other ideas?

 

[SIG357]

I don't think the TOP command is standard SQL, but I know if you're using, say SQL Server, it would go like this

SELECT TOP 5 *
FROM CUSTOMERS;

 

[wood]

Still no luck. It doesn't like Top 5. I am using DB2.

 

[SIG357]

How are you accessing DB2? Through a DRDA Client?

 

[karluk]

You may be better off posting in the DB2 forum. I checked there, and it looks as if you can add a "fetch first 5 rows" clause to your select. See thread178-27947.

 

[karluk]

Another idea is to try the suggestion in faq220-275. That would be an attempt to keep your query ANSI compliant, rather than look for a DB2 specific solution.

 

[Culley]

There is nothing in DB2 compliant SQL that will limit the number of rows returned. It wont take any of the Fetch x Rows stuff. HOWEVER, if your using SPUFI to execute your SQL say "YES" to "Change Defaults". On that panel there is a 3 MAX SELECT LINES ===> value that you can set to whatever. "0" selects ALL lines. "5" would get you only the first 5 lines found. REMEMBER that they data is NOT really in sequence if you do not code an ORDER BY even though it may seem to be!

 

[karluk]

"Fetch first 5 rows" is, indeed, a valid DB2 option. However it apparently was new as of version 7. See, for example

http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid391833_tax285651,00.html

If you're lucky enough to be on DB2 7 this may be an option for you. I still think you would be well advised to post any questions to the DB2 forum. The experts there should be able to help with syntax and version compatibility.

 

[MalcolmW]

Culley,
re: "There is nothing in DB2 compliant SQL that will limit the number of rows returned"
The FAQ that Karluk refered to will do just that. The vendor extension that many databases have (and that DB2 has in V7) to limit the number of rows returned is not ANSI SQL, but is usually easier to code. Malcolm Wynden
Authorized Crystal Engineer

http://www.wynden.net

malcolm@wynden.net