SQL
MySQL Workbench Tutorial for Beginners: How to Install & Use
What is MySQL? MySQL is an open source relational database. MySQL is cross platform which means it...
The limit keyword is used to limit the number of rows returned in a query result.
It can be used in conjunction with the SELECT, UPDATE OR DELETE commands LIMIT keyword syntax
The syntax for the LIMIT keyword is as follows
SELECT {fieldname(s) | *} FROM tableName(s) [WHERE condition] LIMIT N;
HERE
Let's look at an example -
SELECT * FROM members LIMIT 2;
membership_ number full_ names gender date_of _birth date_of _registration physical_ address postal_ address contact_ number credit_ card_ number 1 Janet Jones Female 21-07-1980 NULL First Street Plot No 4 Private Bag 0759 253 542 This email address is being protected from spambots. You need JavaScript enabled to view it. NULL 2 Janet Smith Jones Female 23-06-1980 NULL Melrose 123 NULL NULL This email address is being protected from spambots. You need JavaScript enabled to view it. NULL
As you can see from the above screenshot, only two members have been returned.
Getting a list of ten (10) members only from the database
Let's suppose that we want to get a list of the first 10 registered members from the Myflix database. We would use the following script to achieve that.
SELECT * FROM members LIMIT 10;
Executing the above script gives us the results shown below
membership_ number full_ names gender date_of _birth date_of _registration physical_ address postal_ address contact_ number credit_ card_ number 1 Janet Jones Female 21-07-1980 NULL First Street Plot No 4 Private Bag 0759 253 542 This email address is being protected from spambots. You need JavaScript enabled to view it. NULL 2 Janet Smith Jones Female 23-06-1980 NULL Melrose 123 NULL NULL This email address is being protected from spambots. You need JavaScript enabled to view it. NULL 3 Robert Phil Male 12-07-1989 NULL 3rd Street 34 NULL 12345 This email address is being protected from spambots. You need JavaScript enabled to view it. NULL 4 Gloria Williams Female 14-02-1984 NULL 2nd Street 23 NULL NULL NULL NULL 5 Leonard Hofstadter Male NULL NULL Woodcrest NULL 845738767 NULL NULL 6 Sheldon Cooper Male NULL NULL Woodcrest NULL 976736763 NULL NULL 7 Rajesh Koothrappali Male NULL NULL Woodcrest NULL 938867763 NULL NULL 8 Leslie Winkle Male 14-02-1984 NULL Woodcrest NULL 987636553 NULL NULL 9 Howard Wolowitz Male 24-08-1981 NULL SouthPark P.O. Box 4563 987786553 lwolowitz[at]email.me NULL
Note only 9 members have been returned in our query since N in the LIMIT clause is greater than the number of total records in our table.
Re-writing the above script as follows
SELECT * FROM members LIMIT 9;
Only returns 9 rows in our query result set.
The OFF SET value is also most often used together with the LIMIT keyword. The OFF SET value allows us to specify which row to start from retrieving data
Let's suppose that we want to get a limited number of members starting from the middle of the rows, we can use the LIMIT keyword together with the offset value to achieve that. The script shown below gets data starting the second row and limits the results to 2.
SELECT * FROM `members` LIMIT 1, 2;
Executing the above script in MySQL workbench against the myflixdb gives the following results.
Note that here OFFSET = 1 Hence row#2 is returned & Limit = 2, Hence only 2 records are returned
membership_ number full_ names gender date_of _birth date_of _registration physical_ address postal_ address contact_ number credit_ card_ number 2 Janet Smith Jones Female 23-06-1980 NULL Melrose 123 NULL NULL This email address is being protected from spambots. You need JavaScript enabled to view it. NULL 3 Robert Phil Male 12-07-1989 NULL 3rd Street 34 NULL 12345 This email address is being protected from spambots. You need JavaScript enabled to view it. NULL
Let's suppose that we are developing the application that runs on top of myflixdb. Our system designer have asked us to limit the number of records displayed on a page to say 20 records per page to counter slow load times. How do we go about implementing the system that meets such user requirements? The LIMIT keyword comes in handy in such situations. We would be able to limit the results returned from a query to 20 records only per page.
What is MySQL? MySQL is an open source relational database. MySQL is cross platform which means it...
What is Collection? A Collection is an ordered group of elements of particular data types. It can...
Now that Myflixdb, what's next? Congratulations for your success completion of the SQL tutorial...
What is Record Type? A Record type is a complex data type which allows the programmer to create a...
In this tutorial, you are going to see the detailed description on how to create and execute the...
Aggregate Functions are all about Performing calculations on multiple rows Of a single column of a...