Wyoming Finance

May 18 2018

Limiting and ordering SQL results

#order #by #sql #desc


#

Limiting and ordering SQL results

A few keywords to prettify and organize the results of SQL queries

This is the last tutorial on what I would consider “basic” SQL, that is, queries that operate on a single table. The ORDER and LIMIT functions aren’t hard to figure out; they do what they say they do. Their functionality, for our purposes, is largely cosmetic. So this tutorial will mostly be a continuation of the “Let’s see how complicated we can make our queries” game.

Note: For this SQL lesson, I will be using the Sequel Pro GUI for the MySQL database engine and will be querying the SFPD incident reports categorized as ASSAULT

If you want to see exactly the same results I do, you’ll want to download and import the__ MySQL database of 2003 to 2013 assault reports from the SFPD__.

I’ve also created the SQLite version of that database, which should functionally be the same as the MySQL version.

For both MySQL and SQLite, I’ve also created a database of all the SFPD reports from 2003 to 2013. All the queries should work the same, except you can explore all the different categories of crime. The tradeoff is that the database is much bigger, and so will be slower to download, import, and query. If you’re completely new to all this, I would just go with the assaults database, just so any errors you make don’t take even longer to figure out.

LIMIT the number of results returned

The LIMIT clause takes a number and then limits the number of results to that number. For example, to return just the first row in sfpd_incidents :

Using OFFSET

If you LIMIT your results, but want to show, say, the 10th result instead of the first, you can use the OFFSET keyword:

Note: I’ve almost never used OFFSET outside of building a web application, in which I’m doing something like paginating results (i.e. showing the fifth page of 100 records, 10 records per page). Likely, you won’t need OFFSET for doing data queries.

ORDER BY

The LIMIT clause is frequently used with ORDER. because we generally don’t care about the very first record in a dataset, but the first record according to a specified order. This is not conceptually different than doing a column sort in a spreadsheet.

The ORDER BY clause comes before LIMIT ; and both clauses will typically be the final pieces of a query.

For example, if we aren’t sure that a dataset is chronologically ordered, we can specify that results be sorted by Date :

The ORDER and LIMIT clauses cap off SQL queries as we now know them:

SELECT columns, functions, and aliased columns FROM a table WHERE some conditional statements, including LIKE GROUP BY (optional) columns to group by HAVING (optional) some conditional statements ORDER LIMIT

Here’s an actual example:

Conclusion

Being able to order and limit the result set is handy when we’re trying to find a single answer, such as: “In the year 2008, what was the very first incident report for the Northern district?” or “Show the top 3 districts when it comes to making arresting suspected stalkers”

This concludes our coverage of basic SQL functionality. Make sure you have a good grasp of how to properly construct these queries, because while we won’t be learning much more syntax, we will be constructing the SQL equivalent of Dickensian-length sentences .

Exercises

  1. Find the final ‘AGGRAVATED ASSAULT’ to have been reported to the ‘TENDERLOIN’ district in the year 2010.
  2. Show the top 3 districts when it comes to making arresting suspected stalkers.
Solution 1

Written by admin


Leave a Reply

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