Jump to content

order of matching results


Notretsam
 Share

Recommended Posts

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. 

 

Link to comment
Share on other sites

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.  

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

 

 

Link to comment
Share on other sites

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");

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...