Concepts/Basic/

LIMIT

An introduction to the LIMIT clause.

Sometimes when you write a query against a dataset–especially a SELECT * query–you just want to see a sample of the data stored in the dataset. One reason for just returning a sample is that you don’t want to wait the amount of time it would take to retrieve and render all the data in the dataset into a table. Some datasets have upwards of a million rows and the time it takes to run a SELECT * query against one would be measured in minutes, not seconds. You might just want to see how the values in the columns are formatted so you can write a query restricting the rows returned based on the data. LIMIT is used in a query to return up to a specific number of rows in the results and comes after the WHERE clause if there is one.

If we wanted to take a quick pick at some of the data in the Intakes table, we could write this query which limits the rows returned to 10:

SELECT *
  FROM austin_animal_center_intakes
 LIMIT 10
siyeh/austin-animal-center-statistics-oct-1-2013-to-dec-7-2017 Run query Copy code

And the resultant table would look like:

animal_id name datetime monthyear found_location intake_type intake_condition animal_type sex_upon_intake age_upon_intake breed color
A006100 Scamp 2017-12-07T14:07:00 2017-12 Colony Creek And Hunters Trace in Austin (TX) Stray Normal Dog Neutered Male 10 years Spinone Italiano Mix Yellow/White
A006100 Scamp 2014-12-19T10:21:00 2014-12 8700 Research Blvd in Austin (TX) Public Assist Normal Dog Neutered Male 7 years Spinone Italiano Mix Yellow/White
A191351 Bri-Bri 2015-11-13T15:57:00 2015-11 1912 E William Cannon Rd in Austin (TX) Stray Normal Cat Intact Female 16 years Domestic Longhair Mix Black/White
A322813 Tyson 2015-03-05T14:49:00 2015-03 Austin (TX) Public Assist Normal Dog Neutered Male 11 years Rottweiler Mix Black/Brown
A553074 Jo Jo 2016-03-27T00:04:00 2016-03 3614 Bill Price in Travis (TX) Public Assist Normal Dog Spayed Female 7 years Labrador Retriever/German Shepherd Brown/Black
A672744 Oso 2014-02-17T17:10:00 2014-02 Travis (TX) Owner Surrender Normal Dog Intact Male 3 years Rottweiler Mix Black/Tan
A672744 Oso 2014-02-17T17:10:00 2014-02 Austin (TX) Public Assist Normal Dog Intact Male 3 years Rottweiler Mix Black/Tan
A672745 *Dottie 2014-02-16T11:01:00 2014-02 Mlk And Fm 969 in Austin (TX) Stray Normal Dog Spayed Female 2 years American Staffordshire Terrier Mix White/Red
A672746 Manolo 2014-05-21T13:37:00 2014-05 Austin (TX) Owner Surrender Normal Dog Neutered Male 9 years Jack Russell Terrier Mix White/Black
A672746 Manolo 2014-02-16T11:21:00 2014-02 Travis (TX) Owner Surrender Normal Dog Neutered Male 9 years Jack Russell Terrier Mix White/Black

Note that these results are returned randomly and it is possible that every time you run the query you’ll get a different result set.

Exercises (Continued from previous section)

There are two ways to do these exercises. The first is to use the “Try query” links to test your queries without saving them. The second is to create a data.world project and save your queries to it. If you are reading this documentation and completing the exercises as a tutorial, you will need to create your own project to save your work. Details and instructions are in the SQL tutorial which has instructions for setting up your project and links to all the current exercises.

Exercise 4

Using the immunizations table, write a query to show only the first 100 rows of data.

Try query

Hint

SELECT all the records FROM the immunizations table with the LIMIT 100

See solution