CODEWITHSUNDEEP
mysql
peltors
peltors

Reputation: 53

Problems with sorting MySql query

I have a hard time coming up with a query to sort a result set

See http://sqlfiddle.com/#!2/02b65 for table

Each competitor have three results, one from each station.

I want the result set to be sorted according to:

  1. Total result = station1+station2+station3
  2. Descending on value of station 3
  3. Descending on value of station 2
  4. Descending on value of station 1

A solution I really don't want to implement is to hard code the stations in a table, like 

| id | competitior_id | value_of_station_1 | value_of_station_2 | value_of_station_3

But it would mean a much simpler query to only do a sort on the sum of the three station values, then sort on each station according to my description above.

Please, can anyone help me with this? I'm all open for redesign of the table(s).

Upvotes: 0

Views: 83

Answers (2)

DRapp
DRapp

Reputation: 48139

Per my pending comment on your post...

Each competitor has it's total results across all stations... If there is a "tie" between multiple competitors, you want those with the highest score provided from Station 3 to be sorted to the top (within same "total results" set of competitors), if same there, sort next level based on highest Station 2 score, and finally Station 1 score.

select
      R.competitor_id,
      sum( R.result ) as TotalResults,
      max( case when R.Station = 3 then R.Result else 0 end ) as Station3,
      max( case when R.Station = 2 then R.Result else 0 end ) as Station2,
      max( case when R.Station = 1 then R.Result else 0 end ) as Station1
   from
      Results R
   group by 
      R.competitor_ID
   order by
      TotalResults DESC,
      Station3 DESC,
      Station2 DESC,
      Station1 DESC;

SQL Fiddle query

EDIT PER QUESTION/COMMENT

The MAX() for each station is because you are grouping by each competitor. You don't want to count each Station multiple times as you will have 3 records, and you have to group by all non-aggregate fields (well many SQL-engines require, some don't care and just grab the first instance). So, when the first record is processed, it fills station 1, but 2 and 3 are zero values and you don't want that as your basis of sorting.. then when the second gets processed you have 1 & 2 populated, still sitting on the third. So, by applying a MAX(), you are getting the highest value per each station... Each "0" instance and their actual Result... Make sense now?

Upvotes: 0

user1744166
user1744166

Reputation:

Try this query:

SELECT (SELECT SUM(value_of_station_1+value_of_station_2+value_of_station_3) FROM results B WHERE B.id = A.id) as sum_station, A.* FROM results A ORDER BY sum_station DESC

Here you can view an example: http://sqlfiddle.com/#!2/020b5/2

Upvotes: 2

Related Questions