SQL LIMIT versus FETCH FIRST ROWS

Software

I’m as jaded as the next person when I read that I’ve been doing a rudimentary task wrong this whole time. It’s clickbait churnamism of the worst kind, exploiting people’s fears and insecurities.

Did you know that you’ve been washing your hair wrong this whole time? You’re supposed to hang vertically in your shower from a bar, and let the shampoo run down your legs until it reaches your head. Then, and only then, can you rub the goop into your hair with your elbows, then rinse and repeat by jumping like this through a stream of cold water.

This is, fortunately, not one such example. The entire introduction to this post was completely pointless, just like those aforementioned news stores written by people making dubious claims as to the effectiveness of specific rudimentary activities.

I’ve long performed this SQL query on PostgreSQL, MySQL/MariaDB, and SQLite to get a few records for visual comparison:

SELECT friend FROM addresses
WHERE handle = 'screenbeard'
LIMIT 3;

Turns out, LIMIT isn’t standard ANSI SQL. Hans-Jürgen Schönig introduced me today to the concept of FETCH FIRST:

SELECT friend FROM addresses
WHERE handle = 'screenbeard'
FETCH FIRST 3 ROWS ONLY;

As Hans mentions, “this is actually the correct way to handle LIMIT.”. It works in the PostgreSQL and MariaDB servers I have handy, but SQLite doesn’t like it.

The real reason you’d use it in Postgres though is to invoke WITH TIES to handle duplicate records. Check out his post for some great examples.

Author bio and support

Me!

Ruben Schade is a technical writer and IaaS engineer in Sydney, Australia who refers to himself in the third person in bios. Wait, not BIOS… my brain should be EFI by now.

The site is powered by Hugo, FreeBSD, and OpenZFS on OrionVM, everyone’s favourite cloud infrastructure provider.

If you found this post helpful or entertaining, you can shout me a coffee or buy some silly merch. Thanks!