• 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

key word to sort from 0 to

 
Ranch Hand
Posts: 102
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 102
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Does that query work in you run it directly against the DB (using a command-line client)?
 
sahar sa
Ranch Hand
Posts: 102
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 102
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You're right, that was pseudo-code. Try this: select * from tbuyer where id = (select min(id) from tbuyer)
 
sahar sa
Ranch Hand
Posts: 102
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you dear Uldf!
it works now!
 
I've been selected to go to the moon! All thanks to this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic