Jump to content

order of matching results


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 post
Share on other sites

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

Link to post
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 post
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 post
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 post
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 post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...