Re: How to limit # of rows returned by SELECT

Howard Rothenburg (hrothenb_at_mail.bcpl.lib.md.us) wrote:

: Before the select use the 
:      set rowcount 50
: command.
 

: On Thu, 8 Feb 1996, Screemin! wrote:  

: >
: > Does anybody know if there is a way to limit the number of records
: > returned by a SELECT statement? I have a large ODBC database
: > where I would like to display the first 50 records that match a certain
: > criteria, but I don't want the user to have to wait while the SELECT
: > call processes all 7.2 quintillion records as it is a speed-critical
: > application and has to happen in a couple of seconds.
: >
: > I tried SELECT *, COUNT(*) FROM TABLE WHERE COUNT(*) <=50
: > but of course this didn't work, nor should it logically.
: >
: > Any help would be appreciated!
 

: > Gordon Lawson, Senior Software Engineer
: > Computers Unlimited
: > Billings, MT
: > (406) 255-9500
: > gordon_at_cu.comp-unltd.com
: >

I found the same solution as Howard R.

However, I take it that this is not ANSI-92 compliant. It would be good to have a portable solution. Does anyone know of one?

That aside, is there a way to use this in a subquery? I wan't to be able to update the rows returned, so setting the rowcount, selecting them into a temporary table, and then operating off that table won't cut it. ( Or at least I don't think so. I'm fairly new to SQL).

--
Mike Conner
Milwaukee Software
Greenfield, WI
mconner_at_execpc.com