CODEWITHSUNDEEP
mysqlsqlgroup-bysql-order-by
Katherine C
Katherine C

Reputation: 245

MYSQL GROUP BY / ORDER BY confusion

I am trying to sort my results but my order by statement is being ignored... I understand why but not how to fix it. Here is my code:

SELECT 
    result.station_id as 'Station ID',
    result.date as 'Date',
    result.id as 'ID',
    COALESCE(sum(if(result.parameter = '1', result.result, NULL)),'-') as 'RESULT A)',
    COALESCE(sum(if(result.parameter = '2', result.result, NULL)),'-') as 'RESULT B)',
FROM (  
    SELECT
        event.station_id,
        event.date,
        event.id,
        e_result.result
    FROM event
    Inner Join e_result ON e_result.id = event.id
    WHERE 
         (event.station_id =  '0001') AND `devent`.`date` >= '1999-02-08' AND `event`.`date` <= '2011-12-20') as result 
GROUP BY by result.id
ORDER BY 
     result.station_id ASC, 
     result.date DESC

It is grouping correctly but not sorting the results afterwards... It seems to be sorting the groupings. I need the whole result set (not each grouping) sorted after the grouping is finished

Sample output:

Station Date        ID          Result A    Result B
20      7/6/2009    g003        -           3
12      2/8/1999    g000        19.2        -
12      2/8/1999    g001        19.9        -
12      2/14/1999   g002        19.1        -
17      4/9/2003    i001        22.2        4

Should be

Station Date        ID          Result A    Result B
12      2/14/1999   g002        19.1        -
12      2/8/1999    g000        19.2        -
12      2/8/1999    g001        19.9        -
17      4/9/2003    i001        22.2        4
20      7/6/2009    g003        -           3

Tables:

e_result   id, parameter, result
     g002, 1, 19.1
     g000, 1, 19.2
     g001, 1, 19.9
     i001, 1, 22.2
     i001, 2, 4
     g003, 2, 3

event      station_id, date, id
     20, 7/16/2009, g003
     12, 2/8/1999, g000
     12, 2/8/1999, g001
     12, 2/14/1999, g002
     17, 4/9/2003, i001

Upvotes: 0

Views: 522

Answers (3)

Mike Ryan
Mike Ryan

Reputation: 4374

I have put your query into sqlfiddle, but had to do a lot of adjusting to make it work.

Here is a query that produces the result you want:

select result.station_id as 'Station ID',
    date_format(result.date,'%c/%m/%Y') as 'Date',
    result.id as 'ID',
    if (sum(if(result.parameter = 1, result.result,0)) = 0, 
        null, 
        sum(if(result.parameter = 1, result.result,0)))  as 'RESULT A)',
    if (sum(if(result.parameter = 2, result.result,0)) = 0, 
        null, 
        sum(if(result.parameter = 2, result.result,0)))  as 'RESULT B)'
from 
      (SELECT
        event.station_id,
        event.date,
        event.id,
        e_result.result,
        e_result.parameter
      FROM event
      Inner join e_result ON e_result.id = event.id) as result
group by 
     result.station_id, result.date, result.id
order by 
     result.station_id ASC, 
     result.date DESC;

I reworked the coalesce, I removed the where clause on the inner table result, corrected the 'devent' typo, added the parameter field to the inner table. and a few other things

This gives:

STATION ID    DATE         ID      RESULT A    RESULT B
12            2/02/1999    g002    19.1    
12            2/02/1999    g000    19.2    
12            2/02/1999    g001    19.9    
17            4/04/2003    i001    22.2        4
20            7/07/2009    g003                3

It's all here: http://sqlfiddle.com/#!2/34d87/17

Upvotes: 1

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Well, I can see you don't understand how group by works. When you group by, you lose individual records and put them together in a bag, which allows you to perform aggregate functions, such as count, sum, etc. So, as a rule of thumb, if you are selecting a non-aggregated field, then you should put it in the group by clause.

In your example you're not using any aggregate function so, why would you group?

Additionally, you're having issues with data types. Your station_id in your query seems to be a string, while in the output seems to be an integer. The other way around happens with your dates. My bet is that you're using character fields instead of the appropriate data type for those fields and you're getting them ordered as character fields.

Upvotes: 1

Voislav Sauca
Voislav Sauca

Reputation: 3075

Your query is fine, but is missing one last function in the end:

GROUP BY
 result.station_id

Upvotes: 0

Related Questions