wood burning stoves 2.0*
The moose likes JDBC and the fly likes SQL query???? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL query????" Watch "SQL query????" New topic
Author

SQL query????

Naresh Babu
Greenhorn

Joined: Mar 20, 2001
Posts: 29
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
Ranch Hand

Joined: Apr 11, 2001
Posts: 114
use
Reasultset rs=stmt.executeQuery(query);
if(rs.next())
{
rs.getString("colname");
}
u will get data of only one record
Naresh Babu
Greenhorn

Joined: Mar 20, 2001
Posts: 29

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
Ranch Hand

Joined: Jun 30, 2000
Posts: 1070
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
Ranch Hand

Joined: Apr 11, 2001
Posts: 114
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
Ranch Hand

Joined: Mar 13, 2001
Posts: 60
I think you can simply use:
SELECT 1 FROM TableName WHERE Field='Value'
Dave
Anonymous
Ranch Hand

Joined: Nov 22, 2008
Posts: 18944
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
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
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
 
 
subject: SQL query????