Hello,
I wondered if someone can tell me if this is possible.
I'd like a column in my table called "order" where each row is given a number. 1, 2, 3, 4 etc. BUT I want it always to be in ascending order, with no gaps. So it's different to an "id" column because when you delete a row, the numbers change so that there's no missing number...
Perhaps this would fundamentally go against the way a database works, but let me know if not.
Many thanks in advance!
-- wibblywobbly
SQL ascending order, no gaps...
Moderator: General Moderators
-
wibblywobbly
- Forum Newbie
- Posts: 17
- Joined: Mon Oct 19, 2009 10:11 am
Re: SQL ascending order, no gaps...
Yes, it's quite possible and a common approach for controlling specific ordering. You'd have to either control the numbers in the 'order' column using application code (PHP) or using stored procedures.
By the way, I'd recommend against naming the column specifically 'order' since it is a reserved word in MySQL and you'd have to escape it always or you'll get errors. Maybe something like 'position' or 'rank'
By the way, I'd recommend against naming the column specifically 'order' since it is a reserved word in MySQL and you'd have to escape it always or you'll get errors. Maybe something like 'position' or 'rank'
-
wibblywobbly
- Forum Newbie
- Posts: 17
- Joined: Mon Oct 19, 2009 10:11 am
Re: SQL ascending order, no gaps...
Hey,
That's great news. It's difficult to get the right search terms when looking for information on this on Google. Let's go with "position" as you suggest. Out of the 2 options, which is the easiest to implement? I suppose stored procedures sounds more appealing to me...
What am I looking for to get going with this?
Many thanks for getting back to me, as always, very helpful!
-- wibblywobbly
That's great news. It's difficult to get the right search terms when looking for information on this on Google. Let's go with "position" as you suggest. Out of the 2 options, which is the easiest to implement? I suppose stored procedures sounds more appealing to me...
What am I looking for to get going with this?
Many thanks for getting back to me, as always, very helpful!
-- wibblywobbly
Re: SQL ascending order, no gaps...
Personally I'd implement it in application code, since usually the order could change depending on user input. If that's the case, there are usually two options - either pass the user input as an array of row identifiers to the application code for updating the entire ordering at once, or switch between two positions at a time (meaning a row changes positions with another).
For the first option there are some nice Javascript plugins you could use with, such as jQuery UI sortable (it just so happens its default is returning the order as an array of identifiers so it meshes nicely with that approach).
http://docs.jquery.com/UI/Sortable
For the first option there are some nice Javascript plugins you could use with, such as jQuery UI sortable (it just so happens its default is returning the order as an array of identifiers so it meshes nicely with that approach).
http://docs.jquery.com/UI/Sortable