Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.
Hi all, please see this major site announcement: https://www.boards.ie/discussion/2058427594/boards-ie-2026

mysql ordering

  • 09-01-2007 05:06PM
    #1
    corblimey
    Registered Users, Registered Users 2 Posts: 4,475 ✭✭✭


    Does anybody happen to know what sort of order mysql uses when the ORDER BY field is the same? I have a table with 6 records in it: 
    a    1
b    1
c    1
d    1
e    1
f    1
    if I order by the first field, it's fine, but if I order by the second field, it seems random to me. I thought it would default to the order in which the records were added to the table, but that doesn't appear to be the case. Any clues?


Comments

  • #2
    amen
    Registered Users, Registered Users 2 Posts: 2,781 ✭✭✭amen


    it the data you are ordering on is the same you are going to get random results (same for any db)
    the order it comes back in may depend on the statistics on the table, the last time a row was updated, the physical storage of the data etc
    basically you shouldn't be ordering on a column if all the data is the same in the column


  • #3
    seamus
    Registered Users, Registered Users 2 Posts: 68,173 ✭✭✭✭seamus


    When duplicates/conflicts occur in the ORDER BY, MySQL does an ORDER BY ASC on the Primary Key.

    I'm not sure what it does if there's no primary key.


  • #4
    corblimey
    Registered Users, Registered Users 2 Posts: 4,475 ✭✭✭corblimey


    I have a composite primary key, but it doesn't appear to be using that either.
    the order it comes back in may depend on the statistics on the table, the last time a row was updated, the physical storage of the data etc
    basically you shouldn't be ordering on a column if all the data is the same in the column
    I always assumed it was the order in which the data was added to the table for SQL Server et al. Strange.

    I'll try adding a unique primary key and using that.


Advertisement