Bug #11613 | function to get lowest available id | ||
---|---|---|---|
Submitted: | 28 Jun 2005 13:11 | Modified: | 11 Oct 2005 13:41 |
Reporter: | Emil Obermayr | Email Updates: | |
Status: | No Feedback | Impact on me: | |
Category: | MySQL Server | Severity: | S4 (Feature request) |
Version: | OS: | ||
Assigned to: | Assigned Account | CPU Architecture: | Any |
[28 Jun 2005 13:11]
Emil Obermayr
[30 Jun 2005 11:19]
Geert Vanderkelen
Hallo Emil! Sorry if I don't get your point there, but this does not help at al? mysql> select min(i)-1 from uid; Not sure who you talked to in Karlsruhe, but I know there was beer and cake! :) Cheers, Geert
[30 Jun 2005 11:33]
Emil Obermayr
given to following values: 4 5 7 8 min()-1 would result in 3 which is correct as long as 3 is a valid value, but if you have a lowest allowed value, e.g. 1 with the following values: 1 2 3 5 6 min()-1 would result in 0 which is forbidden and "my" function would give 4 which is ok So the point is to find the "holes" in the range of values with a given lower limit of allowed values. I forgot to note the name of the person I spoke with, lazy me :) But I remember he was german, standing at the front desk on Friday and asked someone hacking on the tables behind.
[5 Jul 2005 13:27]
Geert Vanderkelen
Hi Emil, I digged some old query up I used for similar purpose, changed it a bit.. select @prev := min(i) from uid; select * from (select i, i-@prev as diff, i-(i-@prev)+1 as next, @prev:=i as prev from (select i from uid order by i asc) as u) as uu where diff > 1 limit 1; This does what you think of, I'm not sure if this is actually useful to make a function about such a query. Ofcourse, that's not my call :) These things are rather subjective: do you start from bottom up or filling holes from the top? Do you have date fields or simple int fields? Do you want to go in steps by 2 or 3?... Do you start at 1000 for normal UNIX users or 500 for system users, or or.. IMHO, since we have Stored Procedures and subqueries in 5.0 now.. you can implement such things easily on serverside, in any form you want. What you think? Cheers, Geert
[7 Aug 2005 4:19]
Ben Grabkowitz
I think this is a simpler method of getting what you want: SELECT MIN(tableFoo.uniqueid + 1) AS nextID FROM tableFoo LEFT JOIN tableFoo tf1 ON tableFoo.uniqueid + 1 = tf1.uniqueid WHERE tf1.uniqueid IS NULL
[11 Oct 2005 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".