Win a copy of TDD for a Shopping Website LiveProject this week in the Testing forum!
  • 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
  • Paul Clapham
  • Ron McLeod
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • paul wheaton
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Frits Walraven
Bartenders:
  • Piet Souris
  • Himai Minh

doubt in select statement in mysql database

 
Ranch Hand
Posts: 242
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,
As a beginner, this may be a silly question. Please bear with me.

I have a table with around 12 columns and the primary key an "id" being a sequential number statrting from 1, 2.. etc and the last column status of int type 0 or 1. (mySql databse)

I have to select one row whose status is 0 and the smallest "id" in the ascending order.


id ................. status
1 1
2 1
3 0
4 0
5 0



From the above example I need to pick up the 3 rd record. Could any one help me on this issue?
Thanks
Gopu
 
author & internet detective
Posts: 41073
848
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Gopu,
This query gives you that record as the first element.
<blockquote>code:
<pre name="code" class="core">select * from table where status = 0 order by id;</pre>
</blockquote>

You could have your Java code just read the first row. Or you could use a MySql specific statement to limit the result set to just one row. In MySql, I think the statement is "limit 1" at the end of the query.
 
Gopu Akraju
Ranch Hand
Posts: 242
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Jeanne,

I tried through sql statement as below as per your suggestion.

<blockquote>code:
<pre name="code" class="core">select * from table where status = 0 order by id limit 1;</pre>
</blockquote>

And it works. Thanks again.

Regards
Gopu
 
Arthur, where are your pants? Check under this tiny ad.
free, earth-friendly heat - a kickstarter for putting coin in your pocket while saving the earth
https://coderanch.com/t/751654/free-earth-friendly-heat-kickstarter
reply
    Bookmark Topic Watch Topic
  • New Topic