Skip site navigation (1) Skip section navigation (2)

best way to fetch next/prev record based on index

From: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
To: <andrew(at)pillette(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: best way to fetch next/prev record based on index
Date: 2004-07-27 18:17:15
Message-ID: 6EE64EF3AB31D5448D0007DD34EEB34101AEFE@Herge.rcsinc.local (view raw or flat)
Thread:
Lists: pgsql-performance
> SELECT * FROM t WHERE
> (a >= a1 AND b>=b1 AND c>=c1) ORDER BY a,b,c LIMIT 1 OFFSET 1;
> 
> using the way LIMIT cuts down on sort time (I've never tried it with
both
> LIMIT and OFFSET, though; you could always use LIMIT 2 and skip a
record
> client-side if that works better).

Don't want to further clutter the list (answered this question several
times already), but your query does not work.  What I meant to write
was:

select * from t where
	a >= a1 and
     (a >  a1 or b >= b1) and
     (a >  a1 or b > b1 or c > c1)
    order by a, b, c limit 1

The problem with your query is it excludes all values of c >= c1
regardless of values of a and b.

Merlin

pgsql-performance by date

Next:From: Greg StarkDate: 2004-07-27 18:38:48
Subject: Re: best way to fetch next/prev record based on index
Previous:From: andrewDate: 2004-07-27 17:37:24
Subject: Re: best way to fetch next/prev record based on index

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group