From: Greg Stark <gsstark@mit.edu>
To: Dino Vliet <dino_vliet@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: only last records in subgroups
Date: 23 Aug 2004 00:09:34 -0400
Message-ID: <878yc65wch.fsf@stark.xeocode.com> (raw)
In-Reply-To: <20040817104437.93301.qmail@web40110.mail.yahoo.com>
References: <20040817104437.93301.qmail@web40110.mail.yahoo.com>
Dino Vliet <dino_vliet@yahoo.com> writes:
> x,0 and y,4 but how do I manage this in sql? I was
> hoping for a keyword LAST or so, where I can specify
> that when I've ordered my results with order by, I
> could only get the last of the subgroups (the first
> one is easy because I could use limit 1)
There's no concept of "first" and "last" in SQL outside of the ORDER BY clause
of your query. And you can easily reverse the order of the ORDER BY sort by
putting "DESC" after the columns you're sorting on.
But I don't understand how you intend to use "LIMIT 1" to solve your problem.
As you describe the problem you want the last (or first) record of *each*
*group*. Solving that using LIMIT would require a complex query with a
subquery in the column list which would be quite a pain.
As the other poster suggested, if you're just looking to fetch a single column
you can just use min() or max() to solve this.
If you're looking to fetch more than one column Postgres provides a
non-standard SQL extension for dealing with this situation, "DISTINCT ON".
SELECT DISTINCT ON (id) id,day,other,columns FROM tab ORDER BY id,day
That gets the lowest value of "day". Using "ORDER BY id, day DESC" to get the
greatest value of "day".
--
greg
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gsstark@mit.edu, dino_vliet@yahoo.com
Subject: Re: only last records in subgroups
In-Reply-To: <878yc65wch.fsf@stark.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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