pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Getting 'n-1'th record.
5+ messages / 5 participants
[nested] [flat]
* Re: Getting 'n-1'th record.
@ 1970-01-01 00:00 omid omoomi <oomoomi@hotmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: omid omoomi @ 1970-01-01 00:00 UTC (permalink / raw)
To: bhuvansql@yahoo.com; pgsql-sql
Hi,
It is a not a clean job but how about having a view like this :
create view foo_view as select * from yourtable order by oid desc limit 2 ;
and then making your select like this:
select * from foo_view order by oid limit 1;
hope that helps
Omid
>From: Bhuvan A <bhuvansql@yahoo.com>
>To: pgsql-sql@postgresql.org
>Subject: [SQL] Getting 'n-1'th record.
>Date: Tue, 21 Aug 2001 13:11:33 +0530 (IST)
>
>
>hi all,
>
>consider below..
>
>An sql query results with 'n' records. OK.
>I need ONLY the 'n-1'th record.
>
>
>HOW CAN I GET THIS?
>
>Thankx in advance!
>
>
>======================================================================
> Q: What's the difference between the 1950's and the 1980's?
> A: In the 80's, a man walks into a drugstore and states loudly, "I'd
> like some condoms," and then, leaning over the counter, whispers,
> "and some cigarettes."
>
>
>======================================================================
>
>Regards,
>Bhuvaneswar.
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
^ permalink raw reply [nested|flat] 5+ messages in thread
* Getting 'n-1'th record.
@ 2001-08-21 07:41 Bhuvan A <bhuvansql@yahoo.com>
2001-08-21 14:21 ` Re: Getting 'n-1'th record. Tom Lane <tgl@sss.pgh.pa.us>
2001-08-21 15:08 ` Re: Getting 'n-1'th record. Jeff Eckermann <jeckermann@verio.net>
2001-08-21 15:15 ` Re: Getting 'n-1'th record. Josh Berkus <josh@agliodbs.com>
0 siblings, 3 replies; 5+ messages in thread
From: Bhuvan A @ 2001-08-21 07:41 UTC (permalink / raw)
To: pgsql-sql
hi all,
consider below..
An sql query results with 'n' records. OK.
I need ONLY the 'n-1'th record.
HOW CAN I GET THIS?
Thankx in advance!
======================================================================
Q: What's the difference between the 1950's and the 1980's?
A: In the 80's, a man walks into a drugstore and states loudly, "I'd
like some condoms," and then, leaning over the counter, whispers,
"and some cigarettes."
======================================================================
Regards,
Bhuvaneswar.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Getting 'n-1'th record.
2001-08-21 07:41 Getting 'n-1'th record. Bhuvan A <bhuvansql@yahoo.com>
@ 2001-08-21 14:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2001-08-21 14:21 UTC (permalink / raw)
To: Bhuvan A <bhuvansql@yahoo.com>; +Cc: pgsql-sql
Bhuvan A <bhuvansql@yahoo.com> writes:
> I need ONLY the 'n-1'th record.
See the 'LIMIT' and 'OFFSET' clauses in SELECT. Note the caveat that
you'd better ORDER the rows to be sure you know which is the n-1'th.
Having done an ORDER BY, you could simplify your life by reversing
the ordering and choosing the second row (OFFSET 1 LIMIT 1).
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Getting 'n-1'th record.
2001-08-21 07:41 Getting 'n-1'th record. Bhuvan A <bhuvansql@yahoo.com>
@ 2001-08-21 15:08 ` Jeff Eckermann <jeckermann@verio.net>
2 siblings, 0 replies; 5+ messages in thread
From: Jeff Eckermann @ 2001-08-21 15:08 UTC (permalink / raw)
To: Bhuvan A <bhuvansql@yahoo.com>; pgsql-sql
SELECT *
FROM table
ORDER BY field DESC
LIMIT 1
OFFSET 1;
This way you don't need to know the value of "n" in advance.
The descending ORDER BY is to indicate a reversal of your intended ordering,
so as to make the n-1'th record the second record.
Note that getting the n-1'th record from an unordered list will not give
meaningful results, because you cannot predict what will be returned.
----- Original Message -----
From: "Bhuvan A" <bhuvansql@yahoo.com>
To: <pgsql-sql@postgresql.org>
Sent: Tuesday, August 21, 2001 2:41 AM
Subject: Getting 'n-1'th record.
>
> hi all,
>
> consider below..
>
> An sql query results with 'n' records. OK.
> I need ONLY the 'n-1'th record.
>
>
> HOW CAN I GET THIS?
>
> Thankx in advance!
>
>
======================================================================
> Q: What's the difference between the 1950's and the 1980's?
> A: In the 80's, a man walks into a drugstore and states loudly, "I'd
> like some condoms," and then, leaning over the counter, whispers,
> "and some cigarettes."
>
>
======================================================================
>
> Regards,
> Bhuvaneswar.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Getting 'n-1'th record.
2001-08-21 07:41 Getting 'n-1'th record. Bhuvan A <bhuvansql@yahoo.com>
@ 2001-08-21 15:15 ` Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2001-08-21 15:15 UTC (permalink / raw)
To: pgsql-sql
Bhuvan,
> An sql query results with 'n' records. OK.
> I need ONLY the 'n-1'th record.
You're making this much harder than it needs to be.
If you want the "nth" record, then you have to be supplying the database
with an ORDER BY. For the next-to-last record, simply reverse the ORDER
BY and take the second record.
e.g.:
If you want the next-to-last (n-1) record from:
SELECT * FROM syslog
ORDER BY entrytime;
Then ask for:
SELECT * FROM syslog
ORDER BY entrytime DESC
LIMIT 1 OFFSET 1;
Easy, no?
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2001-08-21 15:15 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1970-01-01 00:00 Re: Getting 'n-1'th record. omid omoomi <oomoomi@hotmail.com>
2001-08-21 07:41 Getting 'n-1'th record. Bhuvan A <bhuvansql@yahoo.com>
2001-08-21 14:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-08-21 15:08 ` Jeff Eckermann <jeckermann@verio.net>
2001-08-21 15:15 ` Josh Berkus <josh@agliodbs.com>
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