SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    3 Post(s)
    0 Thread(s)

    Best way to store ordering

    When I'm storing a list of items, along with the ordering that's been manually assigned, I normally do it like so
    SELECT animal, preference FROM favourite_animals ORDER BY preference ASC
    animal	|preference
    dog	|	1
    goat	|	2
    monkey	|	3
    unicorn	|	4
    But it does get a bit cumbersome when changing the order, as you obviously have to also change the order of any items being displaced. But I really can't think of another way. It's not the worlds most pressing problem, but any ideas an another way to store this kind of data?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Toronto, Canada
    63 Post(s)
    3 Thread(s)
    suggestion 1 (i tried myself) -- number them by 100s

    to move monkey between dog and goat, change its preference to 150

    basically all you have to do is some maths on the higher and lower values which bracket where you want the row to be moved to

    in the long term, if you have ~lots~ and ~lots~ of shuffling around, you might need to resequence the rows

    suggestion 2 (have not tried myself) -- use FLOAT for the sort number

    same maths, but no resequencing will ever be needed | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts