• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL query????

 
Naresh Babu
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 114
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
use
Reasultset rs=stmt.executeQuery(query);
if(rs.next())
{
rs.getString("colname");
}
u will get data of only one record
 
Naresh Babu
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 1070
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 114
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 60
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think you can simply use:
SELECT 1 FROM TableName WHERE Field='Value'
Dave
 
Anonymous
Ranch Hand
Posts: 18944
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic