CODEWITHSUNDEEP
sqlsorting
SuprDewd
SuprDewd

Reputation: 269

SQL Sorting Algorithm

I have the following table:

| WinnerID | LoserID |

It describes the result of a match between two competitors. Currently, I have the following query for generating a high score:

SELECT WinnerID 
FROM KomperResult 
GROUP BY WinnerID 
ORDER BY COUNT(LoserID) DESC LIMIT 10;

If the results were:

| WinnerID | LoserID |
     1          2
     1          3
     4          1

The high score would be:

 1
 4

But I want it to be:

 4
 1

What would be a better algorithm / query for generating the high score?

Upvotes: 2

Views: 547

Answers (2)

Kris Babic
Kris Babic

Reputation: 6304

From your posting and from the comments it appears that you are attempting to create a ranking system based on the results of individual competition. For simple scenarios, such as the one you described, it may be possible to create a query that will return the expected results. However, as you get more results with more users, the number of different scenarios and the calculations required can become more complex.

It might be beneficial to take a look at the Elo Ranking System used by Chess to create a ranking between individuals in one-on-one competitions. Here is a site that give a pretty decent example of how the calculations work: Elo Introduction (explained as used for Go tournaments).

In order to create any type of ranking system you will need to clearly list all of the rules/logic that will affect the overall ranking. Without a complete list a full algorithm cannot be fully flushed out.

Upvotes: 2

JohnP
JohnP

Reputation: 50009

Well, why not sort it the other way then?

SELECT WinnerID 
FROM KomperResult 
GROUP BY WinnerID 
ORDER BY COUNT(LoserID) ASC LIMIT 10;

Haven't validated though

The thing here is, you're sorting by LoserID, but you only want WinnerID. If you know exactly what you want, the data may make sense, but to me this doesn't seem the best way.

Upvotes: 1

Related Questions