• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

SQL query????

 
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 114
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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


 
Ranch Hand
Posts: 1070
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 60
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I think you can simply use:
SELECT 1 FROM TableName WHERE Field='Value'
Dave
 
Ranch Hand
Posts: 18944
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
author
Posts: 3252
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
reply
    Bookmark Topic Watch Topic
  • New Topic