Sometimes I stumble across cool things while solving other problems. Although, often what I think is a neat trick is just my lack of experience in the given technology. That doesn’t stop it being a neat trick! Here’s a nice one-liner to find the last N rows of a database table in SQL.
I’m still such an1 SQL n00b that when I see a solution to a problem, I often think “Wow, that’s cool!”.
One such solution was to a problem I had recently: finding the last N rows in a database table. I didn’t come up with the solution myself; I found it on StackOverflow. The solution is nice because it’s a one-liner and there’s a bit of mental juggling one has to do to work out what’s going on. Doing some brain-juggling isn’t an issue, I find, because SQL keeps making me think in ways I’m not accustomed to. And that’s a Good Thing™.
Anyway, I was wanting to find the last 16 rows in a particular database table (with respect to the row id) and yet still have the rows sorted in the usual ascending order. The solution, in short, looks like this:
WITH t AS (SELECT id FROM users ORDER BY id DESC LIMIT 16) SELECT * FROM t ORDER BY id ASC;
I learned some cool things here. In particular, I came across the WITH and LIMIT commands, which I’d not run into or played with much before.2
So how does one get to a solution like this? Let’s have a look at it in detail.
Ok, we want to get only 16 rows from the users table. To do that, we can SELECT from that table and use LIMIT 16 to get 16 rows. In other words, one can use this query:
SELECT id FROM users LIMIT 16;
The thing is, this will get the first 16 rows in the table. We want the last 16 rows.
Hrm, ok, now what to do? With a bit of thought, we could realise that sorting the output in descending order according to row id (i.e. ORDER BY id DESC) and then selecting the first 16 rows we get back actually gives the last 16 rows in the table. That’s a nice insight there on its own.
That idea looks like this:
SELECT id FROM users ORDER BY id DESC LIMIT 16;
The problem now is, the ids are in the wrong order. We want them in ascending order. Bummer.
Unfortunately, it’s not possible to simply append ORDER BY id ASC to the query above as that wouldn’t be valid SQL. We have to be a bit cleverer. The fix is to turn the query above into a temporary table using a WITH query, and then to select from the temporary table sorted by id in ascending order.
Put another way, we end up doing something looking like this:
SELECT id FROM table_name ORDER BY id ASC;
except where table_name is the result of the original LIMIT query, i.e.:
SELECT id FROM users ORDER BY id DESC LIMIT 16;
The WITH part of our solution,
WITH t AS (SELECT id FROM users ORDER BY id DESC LIMIT 16) ...
creates a “table” called t from the query fetching the last 16 rows. Then all we have to do is fetch all rows of t but this time sorted by id in ascending order.
Putting all this together, we get
WITH t AS (
SELECT id FROM users ORDER BY id DESC LIMIT 16
) SELECT * FROM t ORDER BY id ASC;
where I’ve split the line to highlight the embedded query.
I’ll bet many people will be rolling their eyes at this, thinking “Duh, of course”. Yet, to a newbie, there are some mental gymnastics involved. The juggling routine goes like this: “Turn your table upside down, take the first N rows of that, give that result a name, then turn that up the right way”. I sure as hell wouldn’t have come up with such a solution straight away!
Now back to whatever it is I was wanting to do with those row ids…
Top comments (0)