SQL query????

[Naresh Babu]

Hi,
I am getting set of records at oracle db.But , i need
to get only the first record .Is there any way to get
the first record from set of records.Please ,
suggest me.
Thanks

[prabhat kumar]

use
Reasultset rs=stmt.executeQuery(query);
if(rs.next())
{
rs.getString("colname");
}
u will get data of only one record

[Naresh Babu]

Thanks! for the reply.But, i want to get the first record
at the oracle db using sql query .
Thanks

Originally posted by prabhat kumat:
use
Reasultset rs=stmt.executeQuery(query);
if(rs.next())
{
rs.getString("colname");
}
u will get data of only one record

[bill bozeman]

I am not as familiar with PL/SQL, but in T-SQL you have a TOP command. So you can say something like this:
SELECT TOP 1 field FROM table ORDER BY field
I'll try to find out if this is supported in PL/SQL or if there is some equivalent.
Bill

[This message has been edited by bill bozeman (edited April 20, 2001).]

[prabhat kumar]

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

[Dave Turner]

I think you can simply use:
SELECT 1 FROM TableName WHERE Field='Value'
Dave

[Anonymous]

Using "SELECT 1 FROM tablename WHERE field=value" will generate a result set with the value 1 for each row in the table where field=value.

[Peter den Haan]

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