SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    524
    Mentioned
    3 Post(s)
    Tagged
    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
    Code:
    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
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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