File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes doubt in select statement in mysql database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "doubt in select statement in mysql database" Watch "doubt in select statement in mysql database" New topic
Author

doubt in select statement in mysql database

Gopu Akraju
Ranch Hand

Joined: Jan 13, 2008
Posts: 242
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 29287
    
140

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Gopu Akraju
Ranch Hand

Joined: Jan 13, 2008
Posts: 242
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: doubt in select statement in mysql database
 
Similar Threads
Query Optimization
Table Structure(s)
get the ID number with AUTO INCREMENT
Getting lock on DB from two different JVM
select values that do not have state = x