Concepts/Basic/

OFFSET

An introduction to the keyword OFFSET.

As with the keyword LIMIT, the keyword OFFSET is used to determine which rows in a dataset to return based on their position in the query results, not by their values. OFFSET dictates the number of rows to skip from the beginning of the returned data before presenting the results. OFFSET is primarily used by automated access to page through data in the absence of page up and page down mechanisms.

If we ran a query to show us all the columns in the first 10 rows in a dataset it would look like:

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

The query would return the first 10 rows of the query results, and the results would look like this:

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

If we added an offest to the query it would look like this:

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

The results of the query would be the next set of 10 rows of the query result, and would look like this:

animal_id name datetime monthyear found_location intake_type intake_condition animal_type sex_upon_intake age_upon_intake breed color
A672747 Bella 2014-02-20T16:06:00 2014-02 Austin (TX) Owner Surrender Normal Dog Spayed Female 1 year Australian Kelpie Mix Black/White
A672748 2014-02-16T12:33:00 2014-02 1111 W 34Th St in Austin (TX) Public Assist Normal Other Unknown 3 years Bat Mix Brown/Black
A672749 2014-02-16T12:55:00 2014-02 12705 Lamplight Village in Austin (TX) Wildlife Sick Other Unknown 1 year Raccoon Mix Black/Silver
A672750 Gus 2014-02-16T13:11:00 2014-02 6103 Manor Rd in Austin (TX) Stray Normal Dog Intact Male 2 months Chihuahua Shorthair Mix Brown Brindle/White
A672751 *Simon 2014-02-16T13:40:00 2014-02 2318 Post Oak Rd. in Travis (TX) Stray Normal Dog Neutered Male 11 years Australian Shepherd Mix Black/Brown Merle
A553542 Zeus 2015-04-05T08:45:00 2015-04 Stassney & Westgate in Austin (TX) Stray Normal Dog Neutered Male 7 years Siberian Husky White/Black
A672752 *Lazaro 2014-02-16T13:37:00 2014-02 12900 Carillon Way in Manor (TX) Stray Normal Cat Intact Male 1 month Domestic Shorthair Mix Orange Tabby/White
A672753 2014-02-16T13:36:00 2014-02 Anderson Mill Rd And Olson Dr in Austin (TX) Stray Normal Dog Intact Female 1 year Miniature Schnauzer/Whippet White
A672754 2014-02-16T13:47:00 2014-02 6720 Quinton in Austin (TX) Stray Normal Dog Intact Female 10 months Maltese/Jack Russell Terrier White/Tan
A672755 Lucky 2014-02-16T14:27:00 2014-02 Verbank Villa Dr & Ringsby Rd in Austin (TX) Stray Normal Dog Intact Male 7 years Beagle/Dachshund Tricolor

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 18

Try query

Hint

SELECT everything FROM the immunizations table and LIMIT the results to 100 with an OFFSET of 100 results.

See solution