Re: Q: Limiting number of rows in a select...

In article <mickmEEtuGJ.3Bp@netcom.com>, mickm@netcom.com (Mickey Mestel) wrote: > hi, > > i know this has been posted before, but i can't find it. what is > a good way to limit the number of rows returned by a select, like if you > only want the first 10 or something? > > thanks, > > mickm > ______________________________________________________________________ _______ > Mickey Mestel mickm@netcom.com Mickey, Well, with XPS, you just use "select first 10 ... " but I suppose that isn't an option here. ;-) The non-intuitive statement you are looking for calls for using a correlated subquery with an alias of the table in question. Here's a template: select * from table t1 where 10 > (select count(*) from table t2 where g2.rowid > g1.rowid); Usually, you will replace rowid with some alternative distinct value, like a primary key, so you can get predictable results. Also, when you have a fragmented table, remember that rowids are not reated unless you explicitly specify. Finally, recognize that this can be Very Slow for large tables, so using procedural cursor-based fetches is often preferred. Glenn Miller glenn@tsunami.com co-author: Informix Unleashed -------------------==== Posted via Deja News ====----------------------- http://www.dejanews.com/ Search, Read, Post to Usenet