Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

doubt in select statement in mysql database

 
Gopu Akraju
Ranch Hand
Posts: 242
  • Mark post as helpful
  • send pies
  • 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
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34973
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • 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
  • 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
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic