aspose file tools*
The moose likes JDBC and the fly likes key word to sort from 0 to Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "key word to sort from 0 to" Watch "key word to sort from 0 to" New topic
Author

key word to sort from 0 to

sahar sa
Ranch Hand

Joined: Jul 06, 2009
Posts: 102
Dear Ranchers,
I have a problem can be solve by one of hese ways:
First:
I have an auto-increment key in my table. so what I have in this column is :
1
2
3
4
5
and after deleting some rows, what we will have is :
3
4
5
But I want it to always start by 1 !
is there any key word in mysql to apply to a column and then make it being sort from 0 to ... for all the time(even after deleting)?

second question:
how can I retrive the first row in my DB ?(answer is not id=0 because az I said after some rounds my first row id is not 0)

thanks,
Sahar.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42931
    
  68
As to #1, if those are keys then it shouldn't matter which value they have, and it would be hard to change those after the fact due to foreign keys referencing them. You could add an extra attribute that indicates the "order" or whatever it is that the number would represent. Your code would have to maintain that programmatically, though.

As to #2, something like "select * from table where id = min(id)" should do the trick.
sahar sa
Ranch Hand

Joined: Jul 06, 2009
Posts: 102
dear Ulf,
great idea thnx!
but I have tried in JDBC got some mysql syntax error. I checked in mysql toturial and it comes out:

error:


I dont know! but i didt ask for any column "NAME" in query!!
do you have any idea?

thanks,
sahar.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42931
    
  68
Does that query work in you run it directly against the DB (using a command-line client)?
sahar sa
Ranch Hand

Joined: Jul 06, 2009
Posts: 102
Dear ,
I think Im confuse with my code. can you tell me If i understand this rightly?
This code will return me the whole row which its id is the smallest id in the DB! so what I will have as the result of this query should be the whole of one row?

thank you so much fo your help,
Sahar.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42931
    
  68
The query I posted will do that. The query you posted selects just the smallest ID (but has an extra "group by id" clause that does nothing at all).
sahar sa
Ranch Hand

Joined: Jul 06, 2009
Posts: 102
ok, I try your code it gives me error:

SQLException: Invalid use of group function
SQLState: HY000
VendorError: 1111
java.lang.NullPointerException

thats why i though may I shoul use group by ...!
any idea?
thanks in advance,
Sahar
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42931
    
  68
You're right, that was pseudo-code. Try this: select * from tbuyer where id = (select min(id) from tbuyer)
sahar sa
Ranch Hand

Joined: Jul 06, 2009
Posts: 102
Thank you dear Uldf!
it works now!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: key word to sort from 0 to