Select first record from database

[Sashanan]

Is there an SQL solution to limit the number of results a query returns? Basically I want to be able to select only one record from a database. Preferably the first, but it doesn't really matter. Is there anything I might add to the WHERE part of the query (or somewhere else) that allows me to do this?

 

[Khaldryck]

2 ways of doing it, depending on your database program.
the standard :
select * from table where condition limit 1
or something like that.
i don't know for sure the syntax of the limit

if you're using microsoft access then limit won't work
you have to use aquery like this one :
select top 1 * from table where condition

 

[grega]

As Khaldryck says, it depends on your DB. In Sybase, you can do ...

set rowcount 1
select * from mytable

which will return 1 row.

Greg.

 

[MikeJones]

Just for completeness on Oracle you can do

select *
from my_table
where rownum = 1

however as Oracle doen't garentee the order of rows returned in theory executing this statement twice one after the other could give you two different results.

 

[Sashanan]

Silly me, forgetting to mention which DB I'm using It's Microsoft SQL Server 7.0. The 'set rowcount' variant did the trick here.

Thanks a lot!

 

[grega]

If MS is the same as Sybase, dont forget to set rowcount back to 0 (return all rows) if you're executing more queries in the same session.

Greg.

 

[Sashanan]

Good idea. Actually, it turns out I do not need the set rowcount in this case, it was purely experimental, but I'll keep it in mind for next time.

 

[Cagliostro]

how about
select top 1 ... from ...? John Fill

ivfmd@mail.md

 

[MalcolmW]

The difference between
select top 1 *
from table
is that this also requires an order by clause (for the top N to be meaningful). Therefore this will require a table scan, so the set rowcount solution is faster to execute. Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[Cagliostro]

if you don't put order by, top 1 mean selecting the first from what you select even the records are unsorted(it means physical order) John Fill

ivfmd@mail.md

 

[MalcolmW]

Thanks John - I had it in my head that the Top clause also required an Order by, and is just not the case. Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net