SQL Sorting Algorithm

Question

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?

Team 1 won more often than team 4. Why should team 4 be on top of the list? — recursive
– recursive, Commented Feb 22, 2011 at 16:19
You will need to add more detail as to how you want to view the high score. For example: How does the number of WinnerID records affect the result? How does the WinnerID affect the result? Specifically, why is 4,1 a better result than 1,4? Is is a simple sort by WinnerID in descending order or is it something more complicated? — Kris Babic
– Kris Babic, Commented Feb 22, 2011 at 16:25
looks like 4,1 is better than 1,4 because ID 4 as a win against ID 1 in the direct game between 4 and 1. @SuprDewd: is this what you want? — bw_üezi
– bw_üezi, Commented Feb 22, 2011 at 16:47
Yes, bw_uezi. But I think I solved it with a variation on Kris Babic's answer. — SuprDewd
– SuprDewd, Commented Feb 22, 2011 at 20:22

Kris Babic · Accepted Answer · 2011-02-22 17:39:24Z

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.

JohnP · Accepted Answer · 2011-02-22 16:23:09Z

1

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.

answered Feb 22, 2011 at 16:23

JohnP

50k13 gold badges112 silver badges142 bronze badges

Collectives™ on Stack Overflow

SQL Sorting Algorithm

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related