FETCH - retrieve a portion of rows

We learned about LIMIT clause in this post: LIMIT And OFFSET In Postgres

To limit the number of rows returned by a query, we use the LIMIT clause. Similarly, we can use FETCH clause to do the same.

Syntax:


OFFSET start_point { ROW or ROWS }
FETCH { FIRST or NEXT } [number_of_rows ] { ROW | ROWS } ONLY

The start_point must be a zero or positive integer number.

As we saw in the LIMIT blog post, if the OFFSET clause is not specified then Postgres will consider it as 0. If you provide the start_point higher than the number of rows in result set then you will get zero rows in return, which makes sense.

Value of number_of_rows is one by default. You can set it as 1 or higher values.

When using FETCH, it is important to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows.

Examples:

I am using this database available on my public repo on Github here: https://github.com/ydchauh/yogeshchauhan.com-public

FETCH FIRST ROW ONLY without ORDER BY:


SELECT customer_id, contact_name 
FROM customers 
FETCH FIRST ROW ONLY;

//Output
customer_id, contact_name
"ALFKI"    "Maria Anders"

FETCH FIRST ROW ONLY with ORDER BY:


SELECT customer_id, contact_name 
FROM customers 
ORDER BY customer_id 
FETCH FIRST ROW ONLY;

//Output
customer_id, contact_name
"ALFKI"    "Maria Anders"

Now, if you use FIRST ROW or FIRST 1 ROW, both will give same results.

Let's use the recent example with FIRST 1 ROW:

FETCH FIRST 1 ROW ONLY with ORDER BY:


SELECT customer_id, contact_name 
FROM customers 
ORDER BY customer_id 
FETCH FIRST 1 ROW ONLY;

//Output
customer_id, contact_name
"ALFKI"    "Maria Anders"

Of course, if we want more than 1 row then it is useful. 

FETCH FIRST 3 ROWS ONLY with ORDER BY:


SELECT customer_id, contact_name 
FROM customers 
ORDER BY customer_id 
FETCH FIRST 3 ROW ONLY;

//Output
customer_id, contact_name
"ALFKI"	"Maria Anders"
"ANATR"	"Ana Trujillo"
"ANTON"	"Antonio Moreno"

If you want to skip first row, there is a way, Use OFFSET.

FETCH FIRST 3 ROWS ONLY with ORDER BY but skip the FIRST ROW with OFFSET:


SELECT customer_id, contact_name 
FROM customers 
ORDER BY customer_id OFFSET 1
FETCH FIRST 3 ROW ONLY;

//Output
customer_id, contact_name 
"ANATR"	"Ana Trujillo"
"ANTON"	"Antonio Moreno"
"AROUT"	"Thomas Hardy"

As we can see the first row has been removed from results. I've used FIRST rather than NEXT. Both of them are fine. You can use any word. Also, if you use ROW or ROWS, both are fine.

FETCH FIRST 3 ROWS ONLY with ORDER BY but skip the FIRST ROW with OFFSET (using NEXT):


SELECT customer_id, contact_name 
FROM customers 
ORDER BY customer_id OFFSET 1
FETCH NEXT 3 ROW ONLY;

//Output
customer_id, contact_name 
"ANATR"	"Ana Trujillo"
"ANTON"	"Antonio Moreno"
"AROUT"	"Thomas Hardy"

6 Comments

Winny

Apr 12, 2020 11:04:04 am

Hi there, just became alert to your blog through Google, and found that it's really informative. I am gonna watch out for brussels. I'll appreciate if you continue this in future. Lots of people will be benefited from your writing. Cheers!

Vani

Apr 11, 2020 04:04:17 pm

Hello, this weekend is fastidious for me, since this time i am reading this wonderful informative paragraph here at my house.

Juman

Apr 10, 2020 04:04:14 pm

This is the perfect web site for anyone who would like to understand this topic. You realize so much its almost tough to argue with you (not that I really will need to…HaHa). You definitely put a new spin on a topic that has been discussed for years. Excellent stuff, just wonderful!

juju

Apr 10, 2020 01:04:24 am

always i used to read smaller posts that as well clear their motive, and that is also happening with this post which I am reading now.

Niko

Apr 08, 2020 09:04:42 am

What's up it's me, I am also visiting this web page regularly, this web page is genuinely fastidious and the users are truly sharing fastidious thoughts.

Rameh

Apr 08, 2020 12:04:46 am

Fabulous, what a webpage it is! This website presents useful facts to us, keep it up.

Leave a reply

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