Originally posted by prabhat kumat:
it is very easy ..if ur database support subqueries ..u can do like this..
select * from(select * from emp order by sal) where rownum<2
this is the ultimate soln
Prabhat
"rownum" is indeed the Oracle way to do it, although I don't really see why you would need a subquery.
A quick explanation: "rownum" is a pseudo-column (like rowid, etc). If you take an Oracle query, then the first row returned has rownum 1, the second rownum 2, etc. So you can tell Oracle to only return the first row from the result set by adding "and rownum=1" to the where clause. Much more useful than T-SQL's "top
n" feature.
Nevertheless, you may not want to use it. First, often what you are trying to achieve is get the maximum (or latest, or...) or minimum (... earliest...) of something. The SQL standard "max" and "min" functions are then a much clearer way to formulate that intent (you may have to use grouping). Second, it's Oracle specific. If portability is an issue, you will simply have to avoid it (sometimes you're forced to add a subquery though).
- Peter