Practical introduction to relational databases

Order by, Limit, and Offset

Here we're going to talk about three additions to the where clause: order by, limit, and offset. There are a few other we are not talking about, like group by, and having, but I'll leave the links on this page for you to check out.

Limit and offset

Limit and offset tells the database where to start returning the data from, and how much of it to return. This is useful if you want to have the last of something, or you're paginating results.

The black data table with the squigglies is the data that would be returned by the default select * from table query. The blue window is what's getting returned when using the modifiers of offset = 1 limit = 3.

Offset will skip the first n results that would be returned, and will start returning from the n+1 row.

Limit will keep returning the data until either there's no more data to return, or the number of rows reached the value we set.

This means that if we start from the beginning, and set limit to, for example, 8, and we have 50 rows, we're going to get the first 50 rows.

If we start from row 48 with offset = 48, then we only have 2 rows of data left, so even though we have limit = 8, we'll only have 2 rows in the result set.

You can use limit and offset on their own without the other, and in this context, they come at the very end of the query.

Order by

There are two main ways we can order data:

1. Get everything and do it ourselves on the client side, or
2. Let the database do it for us

Mostly we'll want to do it using the database, simply because it's easier, though that also depends on query performance: generally speaking large sets of data ordered by a column that is not indexed is going to be slow, so in those cases it might be cheaper (as in, take less resources) to do the ordering client side.

To use the order by, you need to specify the name of the column, and optionally whether you want the values to appear in ascending or descending order.

You can also chain order by clauses like this:

SELECT * FROM table ORDER BY col1 desc, col2 asc;

A practical use of this, along with the where clause would be something like "give me the 5 latest completed orders for the user that's currently logged in."

However, let's move on to putting data into tables!