pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT * FROM foo OFFSET -1 LIMIT 1
4+ messages / 4 participants
[nested] [flat]
* SELECT * FROM foo OFFSET -1 LIMIT 1
@ 2005-06-27 15:34 Markus Bertheau ☭ <twanger@bluetwanger.de>
2005-06-27 17:17 ` Re: SELECT * FROM foo OFFSET -1 LIMIT 1 Martín Marqués <martin@bugs.unl.edu.ar>
0 siblings, 1 reply; 4+ messages in thread
From: Markus Bertheau ☭ @ 2005-06-27 15:34 UTC (permalink / raw)
To: pgsql-sql
Hi,
is there a reason that
SELECT * FROM t1 OFFSET -1 LIMIT 1
does not return 0 rows? Accordingly
SELECT * FROM t1 OFFSET -1 LIMIT 2
should return 1 row, imo.
Markus
--
Markus Bertheau ☭ <twanger@bluetwanger.de>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SELECT * FROM foo OFFSET -1 LIMIT 1
2005-06-27 15:34 SELECT * FROM foo OFFSET -1 LIMIT 1 Markus Bertheau ☭ <twanger@bluetwanger.de>
@ 2005-06-27 17:17 ` Martín Marqués <martin@bugs.unl.edu.ar>
2005-06-27 18:41 ` Re: SELECT * FROM foo OFFSET -1 LIMIT 1 Markus Bertheau <twanger@bluetwanger.de>
0 siblings, 1 reply; 4+ messages in thread
From: Martín Marqués @ 2005-06-27 17:17 UTC (permalink / raw)
To: Markus Bertheau ☭ <twanger@bluetwanger.de>; +Cc: pgsql-sql
El Lun 27 Jun 2005 12:34, Markus Bertheau ☭ escribió:
> Hi,
>
> is there a reason that
>
> SELECT * FROM t1 OFFSET -1 LIMIT 1
>
> does not return 0 rows? Accordingly
>
> SELECT * FROM t1 OFFSET -1 LIMIT 2
>
> should return 1 row, imo.
From the docs:
The LIMIT clause consists of two independent sub-clauses:
LIMIT { count | ALL }
OFFSET start
count specifies the maximum number of rows to return, while start specifies
the number of rows to skip before starting to return rows. When both are
specified, start rows are skipped before starting to count the count rows to
be returned.
How do you skip -1 rows?
--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
---------------------------------------------------------
Martín Marqués | Programador, DBA
Centro de Telemática | Administrador
Universidad Nacional
del Litoral
---------------------------------------------------------
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SELECT * FROM foo OFFSET -1 LIMIT 1
2005-06-27 15:34 SELECT * FROM foo OFFSET -1 LIMIT 1 Markus Bertheau ☭ <twanger@bluetwanger.de>
2005-06-27 17:17 ` Re: SELECT * FROM foo OFFSET -1 LIMIT 1 Martín Marqués <martin@bugs.unl.edu.ar>
@ 2005-06-27 18:41 ` Markus Bertheau <twanger@bluetwanger.de>
2005-06-27 19:35 ` Re: SELECT * FROM foo OFFSET -1 LIMIT 1 Greg Stark <gsstark@mit.edu>
0 siblings, 1 reply; 4+ messages in thread
From: Markus Bertheau @ 2005-06-27 18:41 UTC (permalink / raw)
To: Martín Marqués <martin@bugs.unl.edu.ar>; +Cc: pgsql-sql
Dnia 27-06-2005, Mo o godzinie 14:17 -0300, Martín Marqués napisał(a):
> How do you skip -1 rows?
You redefine OFFSET to mean "where in the result set to start returning
rows" and give -1 the meaning that I described above.
Please don't ask me, where in the result set position -1 is.
I've stumbled over this because I needed a query which given an id a
result set like this:
id txt pos
23 foo 0
11 bar 1
34 baz 2
13 xop 3
returns the previous and next rows relative to the given id, as ordered
by the position). That would be 23 and 34 for 11, or NULL and 11 for 23.
I'm basically determining the position of the middle row and then
querying with offset pos-1 limit 1 and offset pos+1 limit 1.
Offset for negative numbers means 0, as it seems. I think there is a
sensible meaning for negative offset numbers and wondered, what
arguments led to negative offsets being processed as 0 offset.
Markus
--
Markus Bertheau <twanger@bluetwanger.de>
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SELECT * FROM foo OFFSET -1 LIMIT 1
2005-06-27 15:34 SELECT * FROM foo OFFSET -1 LIMIT 1 Markus Bertheau ☭ <twanger@bluetwanger.de>
2005-06-27 17:17 ` Re: SELECT * FROM foo OFFSET -1 LIMIT 1 Martín Marqués <martin@bugs.unl.edu.ar>
2005-06-27 18:41 ` Re: SELECT * FROM foo OFFSET -1 LIMIT 1 Markus Bertheau <twanger@bluetwanger.de>
@ 2005-06-27 19:35 ` Greg Stark <gsstark@mit.edu>
0 siblings, 0 replies; 4+ messages in thread
From: Greg Stark @ 2005-06-27 19:35 UTC (permalink / raw)
To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: Martín Marqués <martin@bugs.unl.edu.ar>; pgsql-sql
Markus Bertheau <twanger@bluetwanger.de> writes:
> Offset for negative numbers means 0, as it seems. I think there is a
> sensible meaning for negative offset numbers and wondered, what
> arguments led to negative offsets being processed as 0 offset.
Frankly I'm surprised it's not a syntax error.
--
greg
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-06-27 19:35 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-27 15:34 SELECT * FROM foo OFFSET -1 LIMIT 1 Markus Bertheau ☭ <twanger@bluetwanger.de>
2005-06-27 17:17 ` Martín Marqués <martin@bugs.unl.edu.ar>
2005-06-27 18:41 ` Markus Bertheau <twanger@bluetwanger.de>
2005-06-27 19:35 ` Greg Stark <gsstark@mit.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox