PHP, MySQL, Drupal, .htaccess, Robots.txt, Phponwebsites

31 Jan 2014

Use order by before group by in mysql

                       Normally you can use order by before group by in mysql. Now the question is rise, can you use order by before group b in mysql? Yes you can use order by before group by in mysql. It is possible. You've to use subquery for this. ie,

      SELECT * FROM (
                   SELECT * FROM `table` ORDER BY column_name DESC 
                                    )    t
                                       GROUP BY column_name
Where,
               t is alias because you use subquery. If you don't add it, then you'll get error message "Every derived table must have its own alias".


Group by in Mysql:


                                      Consider the following table. table1 is name of this table.

use order by before group by in mysql

                              Now we are going to get unique values in column 'id' by 'GROUP BY' mysql command like this:

                       SELECT * FROM table1 GROUP BY id 

Now you'll get output like this:

display unique values using group by in mysql


                           You can see the column 'id' values in mysql table 'table1' are unique. The id value 1 are present 1st, 2nd and 5th row. But it takes only the first row.


Use order by before group by:


                            On above example, you want to select highest count of each id and id should be unique. Then you have to use subquery to select highest count values. The mysql query as follows as:

      SELECT * FROM (
                   SELECT * FROM `table1` ORDER BY count DESC 
                                    )    t
                                       GROUP BY id

where,
                 SELECT * FROM `table1` ORDER BY count DESC  run first. It ordered the data in descending order by count.
                 SELECT * FROM ( ... ) t GROUP BY id it takes unique values.
Now you'll get output like this:
               
order by before group by in mysql

                  Now you'll get id with highest count values.


Related Post: