SQL Tutorial part :4 LIMIT Clause

SQL Tutorial part :4  LIMIT Clause
Image

The LIMIT statement is used in conjunction with the SELECT statement. A table in a database can have thousands of rows. If you want to see a few rows from here, then you need LIMIT. With LIMIT you can see a few rows from the middle or some rows from the first side or some rows from any cell.

After LIMIT, two numbers can be used as parameters and the first of the two numbers can be used to determine how many numbers will show data from the row and the second number is for how many rows you want to see.

I have a data table called “locality” here. If you want to see the next 10 rows from the 9th row, then you have to write the following query.

Image

The output will look like the following (you can create a table like yours and insert a lot of data in it to test)

Image

If we want to see the next 15 rows from row number 1

Image

** If you give a parameter (between offset and count) then just take it as count and return count number rows from 1st row. That is

Image

Thus, if given as above, the query will actually be executed as below

Image

So 1st to 15 rows will show. If you want to see the last 15 rows then you can use DESC e.g.

Image

This way you can see any number of rows starting from any row.

An earlier tutorial has shown how to find the highest or lowest value in the returned rows using the aggregate function. Now what if it takes the third highest value? Then it will no longer be with MAX function but with LIMIT. For this, the required number can be found out by sorting, for example, the second highest number has to be found from the table named “students”.

The code for creating the table

Image

Query

Image

It will return the second largest number from among the numbers. If you want to find the maximum third number then LIMIT then 2,1

* If I want to find out the maximum 3 numbers, then LIMIT 0,3. In this way you can filter out the required number as you wish.

* ORDER BY number DESC This is how the numbers have been sorted from big to small before, “ASC” instead of “DESC” will be sorted from small to big.

* Subquiry should be used when maximum second or any number other than LIMIT can be extracted.

Query with SQL WHERE Clause :

WHERE clause is the most necessary and a working clause. Queries can be done very nicely with it. See the table below. We will filter the data seen here in different ways with this WHERE.

Image

The code to create the table with the data

Image

Now suppose those who are above 29 years of age need their information then type the following query and click on “Go”

Image

Output

Image

See only those whose age is over 29 (three people).

Now suppose you only need the data of those whose salary is less than 15000

Image

Continue the query and a row will return, no more pictures.

The following operators can be used to fix the condition in WHERE clause

AND operator example:

Image

The output here will be a row because there is only one person in the data in this table who is 26 years old and has a salary of more than 25000. If there was a lot of data and another person is 26 years old and the salary is more than 25000 then all the rows could be seen with this query.

One thing to note in this query is that there are two conditions in total (age = 27 and salary> 25000) and because of the connection with the AND operator, only those rows will come for which the two conditions will be true, meaning both must be combined. If another condition does not return a row. If a condition is true, if you want to see the data, you have to use the following operator.

Example of OR operator:

Image

Now you will see the output comes in two rows because this query has gone to each row and returned to that row whenever any one of the two conditions match.

Conditions can be created using AND, OR operators together.

There are only 7/8 rows / data in this table, so it may seem unnecessary to create complex queries with brackets here, but when there are thousands of data, they will be very useful. If any one is true, you will need that user, but users who joined after July 11, 2012. Then query like below.

Image

4 rows will return.

When working on large applications, hundreds of such types of information may be displayed on the frontend of the site or somewhere, depending on the needs of the application.

Bring data with BETWEEN clause

==========================

Suppose you need the username and age of the users who joined between 1 and 11 July 2013.

Image

Returns a total of 4 rows with username and age.

Filter repetitive data using DISTINCT in queries to see a row for all rows.

Image

One row will come but if you run this query without DISTINCT then two rows will come.

Leave a Reply

Your email address will not be published. Required fields are marked *