PHP, MySQL, Drupal, .htaccess, Robots.txt, Phponwebsites: Use order by together with group by in mysql

7 Jan 2014

Use order by together with group by in mysql

                       You can use both mysql command 'ORDER BY' and 'GROUP BY' separately. The mysql command 'ORDER BY'  is used to sort the column values and 'GROUP BY' is used to eliminate the duplicate values in column.
                        Can you use both 'OREDER BY' and 'GROUP BY' together in a mysql query?
Yes you can use both mysql command in a query.
                        Why you need to use both mysql command in a query?
If you want to eliminate duplicate values in column and also sort the column in a mysql table, then you've to use these two command in single mysql query. ie,

         SELECT * FROM table GROUP BY column_name ORDER BY column_name

You must use 'GROUP BY' before 'ORDER BY' in mysql. Because 'GROUP BY' get higher preference than 'ORDER BY' in mysql.

Select all values with one value is distinct in mysql table:  

                        You can use 'DISTINCT' mysql command for display unique values in only one column. But the situation, you need to select all columns but one column value is distinct in mysql. Then you've to use both 'ORDER BY' and 'GROUP BY' mysql command in a query. The 'ORDER BY' command should be placed after 'GROUP BY' in mysql query.
Consider the following table. table1 is name of this table.

group by and order by in same mysql query

                              Now we are going to get total count of unique values in column 'id' by 'GROUP BY' mysql command and also order by count in ascending order by 'ORDER BY' mysql command. The mysql query as follows as:

               SELECT id,  SUM(count) as count, date FROM table5 
                                  GROUP BY id ORDER BY count DESC

Now you'll get output like this:

using order by and group by together in mysql

                  You can see the output of the above mysql query which displays total count in descending order.

Related Post:

No comments:

Post a Comment