Home >> FAQs/Tutorials >> MySQL Tutorials >> Index

MySQL FAQs - SQL SELECT Query Statements with GROUP BY

By: FYIcenter.com

Part:   1  2  3   4  5  6 

(Continued from previous part...)

How To Sort the Query Output?

If you want the returning rows to be sorted, you can specify a sorting expression in the ORDER BY clause. The simplest sort expression is column name who's values will be sorted by. The following select statement returns rows sorted by the values in the "counts" column:

mysql> SELECT id, url, counts, DATE(created), tag 
   FROM fyi_links ORDER BY counts;
+-----+-------------------+--------+---------------+------+
| id  | url               | counts | DATE(created) | tag  |
+-----+-------------------+--------+---------------+------+
| 104 | www.mysql.com     |      1 | 2006-01-01    | DBA  |
| 102 | dba.fyicenter.com |      3 | 2006-07-01    | DBA  |
| 101 | dev.fyicenter.com |      4 | 2006-04-30    | DEV  |
| 106 | www.php.net       |      4 | 2004-01-01    | DEV  |
| 103 | sqa.fyicenter.com |      6 | 2006-07-01    | SQA  |
| 105 | www.oracle.com    |      7 | 2005-01-01    | DBA  |
| 107 | www.winrunner.com |      8 | 2003-01-01    | SQA  |
+-----+-------------------+--------+---------------+------+
7 rows in set (0.07 sec)

Can the Query Output Be Sorted by Multiple Columns?

You can specifying multiple columns in the ORDER BY clause as shown in the following example statement, which returns employees' salaries sorted by department and salary value:

mysql> SELECT tag, counts, url, DATE(created) 
   FROM fyi_links ORDER BY tag, counts;
+------+--------+-------------------+---------------+
| tag  | counts | url               | DATE(created) |
+------+--------+-------------------+---------------+
| DBA  |      1 | www.mysql.com     | 2006-01-01    |
| DBA  |      3 | dba.fyicenter.com | 2006-07-01    |
| DBA  |      7 | www.oracle.com    | 2005-01-01    |
| DEV  |      4 | dev.fyicenter.com | 2006-04-30    |
| DEV  |      4 | www.php.net       | 2004-01-01    |
| SQA  |      6 | sqa.fyicenter.com | 2006-07-01    |
| SQA  |      8 | www.winrunner.com | 2003-01-01    |
+------+--------+-------------------+---------------+
7 rows in set (0.00 sec)

How To Sort Output in Descending Order?

If you want to sort a column in descending order, you can specify the DESC keyword in the ORDER BY clause. The following SELECT statement first sorts the "tag" in descending order, then sorts the "counts" in ascending order:

mysql> SELECT tag, counts, url, DATE(created) 
   FROM fyi_links ORDER BY tag DESC, counts;
+------+--------+-------------------+---------------+
| tag  | counts | url               | DATE(created) |
+------+--------+-------------------+---------------+
| SQA  |      6 | sqa.fyicenter.com | 2006-07-01    |
| SQA  |      8 | www.winrunner.com | 2003-01-01    |
| DEV  |      4 | dev.fyicenter.com | 2006-04-30    |
| DEV  |      4 | www.php.net       | 2004-01-01    |
| DBA  |      1 | www.mysql.com     | 2006-01-01    |
| DBA  |      3 | dba.fyicenter.com | 2006-07-01    |
| DBA  |      7 | www.oracle.com    | 2005-01-01    |
+------+--------+-------------------+---------------+
7 rows in set (0.01 sec)

How To Use SELECT Statement to Count the Number of Rows?

If you want to count the number of rows, you can use the COUNT(*) function in the SELECT clause. The following tutorial exercise shows you some good example:

mysql> SELECT COUNT(*) FROM fyi_links;
+----------+
| COUNT(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM fyi_links 
   WHERE url LIKE '%fyi%';
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
1 row in set (0.01 sec)

So there are 7 rows in total in table "fyi_links", and 3 rows that have 'fyi' as part of their url names.

(Continued on next part...)

Part:   1  2  3   4  5  6