Ordered data in a database, how?



  •  Hello :)

    Say I have a certain data set with data which always has to maintain in a specific order? Let's say a list of countries: 1. United Kingdom, 2. Netherlands, 3. Sweden, 4. Norway. Now I want to be able to move them around too. For example put Norway between UK and Netherlands, or swap Sweden and Norway.

     How would I save this in a database? I tried Googling (because it's my friend) but I can't find the correct terms to get the results I need.

    Currently I have an extra field in my table called "sort" and it contains a number at which "spot" in the list it should be. So in the above example UK has 1, Holland has 2, etc. Then whe I move an item up, I swap the two items in three queries (1 to get the "other" number and 2 to assign the other numbers).

    For example if I want to move the Netherlands up first I select from the table where sort < 2 (limit 1), which gives me UK with sort 1. Then I update to set Netherlands to 1. Then I update again to set UK to 2.

     Is there any other, better way? Or is this the way to do it?



  • Nah, that's pretty much the way to do it.



  • Personally I'd have a table to hold the sort orders you want to use, and join that to your normal query. It'd help if we knew which DB you were using. Columns: values_to_join_against, priority, sort_id;



  • @Lingerance said:

    Personally I'd have a table to hold the sort orders you want to use, and join that to your normal query. It'd help if we knew which DB you were using. Columns: values_to_join_against, priority, sort_id;
     

    Why would you put them in a seperate table? I can't remember much of normalization forms and such, because that class was a long time ago, but I'm pretty sure the column would end it up in the same table? :)

    I'm using MySQL for the project that this is relevant to. Or didn't you mean that by which DB I am using? I'm not sure what difference that makes.



  • @pbean said:

    Why would you put them in a seperate table? I can't remember much of normalization forms and such, because that class was a long time ago, but I'm pretty sure the column would end it up in the same table? :)
    The example layout I gave allows for switching the order by just changing which sort_id you use, sorry I wasn't clear. This is what I was expecting the contents to be like:

    country | priority | sort_id
    US      | 3        | 0
    UK      | 1        | 0
    DE      | 2        | 0
    US      | 2        | 1
    UK      | 3        | 1
    DE      | 1        | 1
    

    This has the obvious advantage of the column in the table model of not requiring an UPDATE everytime you want to sort.
    @pbean said:

    I'm using MySQL for the project that this is relevant to. Or didn't you mean that by which DB I am using? I'm not sure what difference that makes.

    Certain databases might actually have a feature that allows this, or there could be other tricks that need to/could be used to make it work nicely.



  • @Lingerance said:

    Personally I'd have a table to hold the sort orders you want to use, and join that to your normal query. It'd help if we knew which DB you were using. Columns: values_to_join_against, priority, sort_id;

    This is also one of those things where it helps to know what the sort is for. Generally when you want a specific order, it's because you're sorting by something... it might be a good idea to back up a step and explain exactly what you're trying to accomplish (big-picture accomplish).

    Like, if you're sorting by GNP, you can just add a GNP column (or join to one) and use ORDER BY.



  • @blakeyrat said:

    @Lingerance said:
    Personally I'd have a table to hold the sort orders you want to use, and join that to your normal query. It'd help if we knew which DB you were using. Columns: values_to_join_against, priority, sort_id;
    This is also one of those things where it helps to know what the sort is for. Generally when you want a specific order, it's because you're sorting by something... it might be a good idea to back up a step and explain exactly what you're trying to accomplish (big-picture accomplish).

    Like, if you're sorting by GNP, you can just add a GNP column (or join to one) and use ORDER BY.

    My response was based on his description, which implied an arbitrary sort order.  Yes, if the developer had specific, static (or relatively static), values by which to sort, storing those values and in the table and then referencing them in the ORDER BY clause would be the best solution.



  • I worry about questions without a big-picture "what I am trying to accomplish with this", because if you gauge that wrong, you could give a correct-but-not-for-that answer.



  • Also, I'd like to point out that sort order is not guaranteed for queries without an Order By clause. In theory, your DB could spit out a totally random list, even if the table looks sorted in your GUI editor.



  •  @blakeyrat said:

    Also, I'd like to point out that sort order is *not guaranteed* for queries without an Order By clause. In theory, your DB could spit out a totally random list, even if the table looks sorted in your GUI editor.

    The way I read it, he has a list that he wants the user to be able to sort, like you would place items in a menu. This means you have to store that order, and then this solution is perfectly valid.

     But I could be totally wrong of course ;)



  • @b-redeker said:

     @blakeyrat said:

    Also, I'd like to point out that sort order is *not guaranteed* for queries without an Order By clause. In theory, your DB could spit out a totally random list, even if the table looks sorted in your GUI editor.

    The way I read it, he has a list that he wants the user to be able to sort, like you would place items in a menu. This means you have to store that order, and then this solution is perfectly valid.

     But I could be totally wrong of course ;)

     

    What blakeyrat says is true; the order of rows returned by the database is not guaranteed and has no meaning outside of being the order they are returned in (i.e. the order in the database is what it is).  If you want your rows to be returned in a specific and repeatable order then (as people have pointed out) you need to have some column in the table that you can sort on using the ORDER BY clause.

    That is, the order can be stored (e.g. using a column called DISPLAY_ORDER) but the data will not be stored in order (i.e. in the database).

     




Log in to reply