order of matching results

[Notretsam]

This isn't a bug or a how too inquiry,  I know how to order items with mysql queries.

What I am wondering though is,  how does mysql determine what result goes first between 2 matching results?

A quick example,  I do a SQL Query ordering by "total wins", so a numeric value. 

2 people have 7 wins each,  how does the SQL Query determine the order for the 2 people? 

In my testing of leagues that am coding, I actually have two divisions of 8 people with 7 wins/ 7 losses each,   always showing on leaderboard at same position.

I am thinking its ordered by who was added first to database table?  but am not sure if mysql has the capability to remember that? 

As I said, its not a bug or a big deal really, am just curious on this.  I also figured I might get asked in future by someone playing my game. 

 

[niche]

My experience of how mysql determines what result goes first varies, that why I use the ORDER BY keyword.

[Ingolme]

It probably depends on the database engine. I believe it may try ordering by the primary key if the rest of the fields are the same.

[Notretsam]

13 minutes ago, Ingolme said:

It probably depends on the database engine. I believe it may try ordering by the primary key if the rest of the fields are the same.

I'll have to double check that.   the primary key is a unique ID for each entry, so numeric value , could be that.  

[justsomeguy]

You can always have it order by multiple columns so if multiple rows have the same value for the first sort column then it will use the next, etc.  Otherwise, consider the result of the sort undefined and subject to change at any point.  If you need it in a specific order then specify the order.

[Notretsam]

doesn't seem like it is by primary,  I changed primary from unique id to seed, which seed is basically a number from 1 to 16.  I changed some numbers around manually in phpmyadmin for seed, and it didn't change the order the items came out,  same as always.

On 1/24/2018 at 12:55 AM, justsomeguy said:

You can always have it order by multiple columns so if multiple rows have the same value for the first sort column then it will use the next, etc.  Otherwise, consider the result of the sort undefined and subject to change at any point.  If you need it in a specific order then specify the order.

I didn't know you could order by more than one column, that would be handy, have to look into that.  thanks

 

 

[Notretsam]

On 1/24/2018 at 12:55 AM, justsomeguy said:

You can always have it order by multiple columns so if multiple rows have the same value for the first sort column then it will use the next, etc.  Otherwise, consider the result of the sort undefined and subject to change at any point.  If you need it in a specific order then specify the order.

well that was easy to do,  thank you

$stmtgetwrest = $connnewish->prepare("SELECT wreid, wretotmats, wrewins, wreloses, wrepoints, wrepercent FROM leagueDivisions WHERE leagueid=? AND leagueseason=? AND divisid=? ORDER BY wrepoints DESC , wreseed");