How can I limit the number of recordsets returned?

[no137]

Newbie Question:
How can I limit the number of recordsets returned from a select statement like "select * from table"? I only want the first 50 records.

Thanks
Fred

 

[r937]

sounds like the "top n" problem

since this is the ansi sql forum, i shall give the ansi sql solution first

select foo
     , bar 
  from yourtable xxx
 where 50 >
       ( select count(*)
           from yourtable
          where bar > xxx.bar
       )
order by bar desc

if you have mysql or postgresql, use this instead --

select foo
     , bar
  from yourtable
order by bar desc
 limit 50

if you have microsoft sql/server or access, use this instead --

select top 50
     , foo
     , bar
  from yourtable
order by bar desc

there are solutions for oracle and db2, holler if you need them

rudy

 

[petersJazz]

Just what to point out that the solution

select foo
     , bar 
  from yourtable xxx
 where 50 >
       ( select count(*)
           from yourtable
          where bar > xxx.bar
       )
order by bar desc

is a performance killer (in most RDBMS).

The normal standard SQL solution to this is programming the search and just fetching the first 50 rows.