Data Display Control

4.1 LIMIT

If you expect your SQL query to return too much data, you can limit the maximum number in advance. After all, tables can contain thousands or even millions of rows.

SQL makes it very easy to limit the number of rows in the result of a query using the LIMIT. This keyword is used at the very end of the SQL query and looks like this:

LIMIT quantity

Let's write a query against the product table, which contains a list of our store's products. But since we have a very successful store and we sell a lot of products, we want to get only the first 10 lines of the answer. Here's what that request would look like:

SELECT * FROM product
LIMIT 10

And we get the following query result:

Now we will request only the first 5 rows. Here's what that request would look like:

SELECT * FROM product
LIMIT 5

We get the following query result:

4.2 OFFSET

Last time we learned how to limit the number of lines in the server response. And now, instead of thousands of rows from the product table, we can get the first 10 rows and display them to the user. It is perfectly. But how do we display the second 10 rows? And the third?

If we write LIMIT 20, or LIMIT 30, won't the server return 20 and 30 rows respectively?

It is for this case that SQL has a special keyword OFFSETwith which you can ask the server to skip N lines in its response. This keyword is used at the very end of the SQL query (after LIMIT) and looks like:

OFFSET quantity

Let's write a query on the product table and try to get records 11 to 15. In other words, we are asking the server to return 5 rows to us, but skip the first 10 rows of the response first. Here's what that request would look like:

SELECT * FROM product
LIMIT 5
OFFSET 10

We get the following query result:

The server returned only 3 rows, which means there were 13 rows in total in our table. The server skipped the first 10 and returned the next 5.

4.3 SELECT DISTINCT

Another useful keyword is DISTINCT. It is used when there are duplicate records in the table and we want to remove the duplicates.

Where do duplicates come from in our table? There may not be duplicates in the table, but as a result of the query, it is easy. Let's write a query that will return the brands of all the products we sell. Here's what that request would look like:

SELECT brand FROM product

We get the following query result:

It is easy to see that there are duplicate rows in this table. It is to ensure that there are no duplicates and you need to use the keyword DISTINCT. Here is what the corrected request will look like:

SELECT DISTINCT brand
FROM product

We get the following query result:

So much better. Is not it? ;)

4.4 ORDER BY

And finally we got to sorting . To sort the results of a query, you need to use the keyword ORDER BY. Technically, these are two words, but the essence is not important. Two words, one operator.

It must be written after the keyword WHERE, but before LIMITand OFFSET. If you write ORDER BYafter LIMIT, then the SQL server will simply return an error to you.

In general, this operator looks like this:

ORDER BY column order

Where column is the name of the column in the table / sort result. And the order is the sort order.

The sort order can be of three types:

• ASC(from Ascending) - ascending;
• DESC(from Descending) - descending;
• Not specified - will be usedASC

Let's write a query that will return a list of products sorted by price in ascending order. Here's what that request would look like:

SELECT * FROM product
ORDER BY price ASC
LIMIT 10

We get the following query result:

We will get the same result if we do not specify the sort order - we will skip the word ASC.

4.5 ORDER BY across multiple columns

The operator ORDER BYallows you to sort the result by several columns. This can be very useful when the first column has a lot of duplicate elements. The general form of this operator is as follows:

ORDER BY column 1 order 1, column 2 order 2, column 3 order 3

In other words, if after sorting by the first column you have several identical values ​​nearby, then they are sorted by the second column, and so on.

Let's write a query that will sort our products by brand, and for products with the same brand - in descending order of their number. Here's what that request would look like:

SELECT * FROM product
ORDER BY brand ASC, count
DESC

We get the following query result:

Look closely at the table - its rows are sorted by brand name (alphabetical order), and rows with the same brand name are sorted by product number in descending order (last column).