How to Sort and Group in SQL

Introduction:

SQL (Structured Query Language) is a domain-specific language that is used in programming to manage data that is stored in a relational database management system. It was initially developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s. The system was designed in order to manage and retrieve data that was stored in IBM’s original relational database management system, System R. SQL helped introduce the concept of accessing many records with one command and eliminated the need to specify how to reach a record. SQL can be used to create and modify databases in addition to querying databases.

Sorting in SQL:

In SQL, you can use the ORDER BY function to sort your results in ascending or descending order. Ascending order means that, for numbers, the smallest one would appear first while words would be listed in alphabetical order from A to Z. By default, the ORDER BY function will sort your results in ascending order. In order to sort the results in descending order, you would use DESC. To effectively sort results in descending order, the ORDER BY function should be entered first, followed by the column that is to be sorted and the DESC function. This would look like:

ORDER BY ________ DESC;

You can also the ORDER BY function to sort multiple columns. To do this, you would use the ORDER BY function followed by the columns, separated by commas, that you want to sort by. The order of the columns listed is important as the ORDER BY function will sort on the first column specified, then the next one and so on.

Grouping in SQL:

GROUP BY is a function in SQL that helps you to aggregate results. For example, if you have a dataset of movies and you want to count how many movies were directed by each director, you could use the GROUP BY function to get your results. In this example, it could also be helpful to use the ORDER BY function to sort your results:

In addition to the COUNT function, the GROUP BY function is also commonly used with other aggregate functions like MAX and MIN.

Limiting the Results:

There are often times where you may only want to display a certain number of results in a query. For example, maybe you only want to display the ten directors that produced the most movies rather than every single director in the dataset. In order to do this, you can use the LIMIT function followed by the number of rows that you want to display.

References:

• Chamberlin, Donald (2012). “Early History of SQL”. IEEE Annals of the History of Computing. 34 (4): 78–82. doi:10.1109/MAHC.2012.61. S2CID 1322572
• “Welcome to the Course!: SQL.” Campus.datacamp.com, campus.datacamp.com/courses/introduction-to-sql/sorting-and-grouping.