OFFSET | SQL Tutorial Documentation on data.world
Concepts Basic Learn SQL Basics The SELECT and FROM Clauses The SELECT * EXCLUDE and SELECT * RENAME Clauses LIMIT WHERE and Comparison Operators ORDER BY AND, OR, and NOT Arithmetic Operations IS NULL and IS NOT NULL LIKE and NOT LIKE BETWEEN OFFSET Intermediate Working With Joins, Aggregations, and Built-In Functions Joins CAST Aggregations GROUP BY and FILTER HAVING UNION, INTERSECT, and MINUS POSITION CASE IN and NOT IN Working With Strings Working With Dates Working With Arrays Advanced WITH Inline Subqueries Value blocks Special Uses of Inline Subqueries Advanced Aggregate Functions Windowed Aggregations ROLLUP and CUBE PIVOT UNPIVOT TABLESAMPLE Metadata Tables Lexical Issues data.world Specific Multi-Tables Federated Queries Parameterized Queries Special Columns Query past instances of data Text formatting for query outputs Reference Boolean Aggregations Regression Aggregations Standard Aggregations Statistical Aggregations Other Aggregations PRAGMA Statements All Functions Array Functions Date Functions Color Functions Formatting Functions General Functions Hashing and Related Functions Linked Data Functions Math Functions Security Functions String Functions URL Extraction Functions Aggregations APPROX_DISTINCT APPROX_PERCENTILE APPROX_MEDIAN ARRAY_AGG ARBITRARY AVG BOOL_OR BOOL_AND CHECKSUM CORRELATION COUNT COUNT_IF COVAR_POP COVAR_SAMP CUME_DIST DENSE_RANK FIRST_VALUE GROUP_CONCAT GROUPING KURTOSIS LAST_VALUE LAG LEAD MAX MAX_BY MIN NTH_VALUE NTILE MIN_BY PERCENT_RANK RANK REGR_AVGX REGR_AVGY REGR_COUNT REGR_R2 REGR_INTERCEPT REGR_SLOPE REGR_SXY REGR_SXX REGR_SYY ROW_NUMBER SKEWNESS STD_POP STDEV SUM VAR_POP VAR_SAMP VARIANCE STD_SAMP Functions ABS ARRAY ARRAY_CONCAT ARRAY_JOIN ARRAY_LENGTH ARRAY_CONTAINS ARRAY_APPEND ARRAY_LOWER ARRAY_PREPEND ASIN AT_TIME_ZONE ARRAY_UPPER ATAN ATAN2 ATTR_OF BASE64_TO_HEX BOLD CEILING CHAR COALESCE COLOR CONCAT COS COSH CURRENT_USER_IN_GROUP CURRENT_USER DATE_ADD DATE_DIFF DATE_PARSE DATE_COMPARE DATE_FORMAT DATE_PART DATE_SUB ELEMENT_AT DAY DEGREES DATE_TRUNC ENDSWITH EXP ACOS EXP10 FLOOR HEX_TO_BASE64 GREATEST HOURS GENERATE_SERIES IF IRI_TO_RESOURCE_HASH IRI_OF ITALIC LABEL_OF JARO_WINKLER_DISTANCE LEAST LEFT LENGTH LEVENSHTEIN_DISTANCE LOG LOWER LOG10 LPAD MD5 LTRIM MINUTES MOD MONTH MID NOW NOW_UTC PI POSITION POW RADIANS RAND RANDOM REGEX REGEXP_EXTRACT REPLACE REVERSE RIGHT RGB ROUND RTRIM SECONDS SHA1 RPAD SHA384 SHA512 SHA256 SIGN SIN SOUNDEX SINH SQRT SOUNDEX_DISTANCE STARTSWITH STR_AFTER STATUS STR_BEFORE STR_CONTAINS STRING_SPLIT SUBSTRING TAN TANH TRIM TRUNC UPPER URL_EXTRACT_FRAGMENT URL_EXTRACT_HOST URL_EXTRACT_PARAMETER URL_EXTRACT_PORT URL_EXTRACT_PATH URL_EXTRACT_QUERY WITH_BACKGROUND_COLOR URL_EXTRACT_PROTOCOL WITH_COLOR WITH_LINK YEAR WITH_STATUS 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 Next up: Working With Joins, Aggregations, and Built-In Functions An introduction to the terminology used in querying multiple tables simultaneously.