Question for SQL experts

[Argo]

Is there a way in SQL to specify how many rows you would like to be returned. One example, where this could be useful is when you want to make sure that a certain record exists in a table. You would specify that you're only looking for 1 row and ideally database engine would stop searching as soon as it finds it (which on average would be middle of the table). For big tables this could make a lot of difference. Other times, I would be interested in obtaining the top/botton 100 rows from a certain table.

Any ideas?

 

[Haircut]

It's not ANSI SQL, but some databases will allow you to do it.

I think in MS SQL server there is a TOP(x) function that will return the first x rows of the table. I've never used SQL server myself, so I'm not certain.

In Oracle you can use rowid in certain situations when you want to return the first few rows of a table.

 

[Lint21]

I've used SQL Server, but I don't know what you're using. There is a TOP command:

select top 100 *
from table (nolock)

You could probably get the bottom 100 rows like this:

select top 100 *
from table (nolock)
order by column DESC

As for your question about having a search stop as soon as the query returns one row, look into the EXISTS (or NOT EXISTS) command. This does exactly what you're asking. Here's a rough example:

select *
from table t (nolock)
where exists
(select 1
from nexttable n (nolock)
where t.column = n.column)

There's also IN and NOT IN which are similar to EXISTS.

LMK if some of this doesn't make sense.

 

[TheLogLady]

Originally posted by: Lint21
I've used SQL Server, but I don't know what you're using. There is a TOP command:

As for your question about having a search stop as soon as the query returns one row, look into the EXISTS (or NOT EXISTS) command. This does exactly what you're asking. Here's a rough example:

select *
from table t (nolock)
where exists
(select 1
from nexttable n (nolock)
where t.column = n.column)

There's also IN and NOT IN which are similar to EXISTS.

LMK if some of this doesn't make sense.

Hi Lint21,

I am far from an SQL expert so I could very well be wrong about this, but wouldn't the above query return the entire table if t.column=n.column? The "column = column" predicate only applies to the subquery, so there is nothing to limit the outer query if the subquery returns '1'.

 

[Jeraden]

I use Oracle.

select *
from table
where rownum <= 100

that will get you the first 100 rows... if you want the last 100 you can do something like:

select * from (
select * from table
order by whatever desc
)
where rownum <= 100

 

[Barnaby W. Füi]

With MySQL you can use LIMIT.

SELECT foo FROM bar LIMIT 0, 100

something like that anyways.