Learn SQL: Advance Select - HOW ORGANIZE MESSY DATA BY USING SELECT

HOW ORGANIZE MESSY DATA BY USING SELECT

There are different ways by which we are able to organize the data. Some of them are:

• ORDER BY
• GROUP BY

ORDER BY:

ORDER BY allows you to order the column alphabetically if applied on alphabetic data and numerically if the data is numeric.
Syntax of order by is: 

SELECT * FROM table_name ORDER BY col_name;

(If you want to select particular column then instead of  * put the columns name.)

Example:

• You can also use ORDER BY with multiple columns.

Example:

For arranging the data in ascending and descending form you can use ASC for ascending order and DESC for descending order with ORDER BY.

Example:

In above Example we wish to arrange the salary in Ascending order but the query that we written firstly arrange the data by name because we use ORDER BY Name and in accordance with the name the salary is arranged.
There are two persons having same name but different salary , the effect of ASC or DESC is shown only in that place for above example.

GROUP BY:

It is used with functions like sum, count, max, min etc. to group the result set by one or more column.

Example:

In above example we show what happen if we use GROUP by with max and what happen if we don't use it.

For Selecting distinct element:

If you want to select distinct element then use DISTINCT Command.
Example:

• When we write first query SELECT DISTINCT Age FROM employee; , then we get all the distinct ages. As, in starting we have two people that have same age but now only one person's age is reflected.
• In Second query, we add ORDER BY , so, the data is reflected on the basis of this.
• In third, we use DISTINCT with GROUP BY, so we get the data of the persons having distinct age but GROUP BY is also accompanied by it. So, the persons having same name is grouped into one. So, we get 5 rows ( initially we have 7).

You can also apply limits on the data to be reflect by using LIMIT.
Example:

QUESTIONS:

1. What is the use of ORDER BY?
2. What is the use of GROUP BY?
3. DESC is used in two ways. Explain.

Previous                                                                                                              Next