pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedonly last records in subgroups
3+ messages / 3 participants
[nested] [flat]
* only last records in subgroups
@ 2004-08-17 10:44 Dino Vliet <dino_vliet@yahoo.com>
0 siblings, 2 replies; 3+ messages in thread
From: Dino Vliet @ 2004-08-17 10:44 UTC (permalink / raw)
To: pgsql-sql
Hi there,
I'm having hard times with the following query:
I want to select only the last records from a subgroup
in a table. But because the subgroup contains
different number of records for every id, I don't know
how to specify that. For example, check the following
table,
id, day
x,300
x,250,
x,0
y,250
y,4
I only want the records
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)
Thanks in advance
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: only last records in subgroups
@ 2004-08-17 11:35 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Dino Vliet <dino_vliet@yahoo.com>
1 sibling, 0 replies; 3+ messages in thread
From: Achilleus Mantzios @ 2004-08-17 11:35 UTC (permalink / raw)
To: Dino Vliet <dino_vliet@yahoo.com>; +Cc: pgsql-sql
O kyrios Dino Vliet egrapse stis Aug 17, 2004 :
> Hi there,
>
> I'm having hard times with the following query:
> I want to select only the last records from a subgroup
> in a table. But because the subgroup contains
> different number of records for every id, I don't know
> how to specify that. For example, check the following
> table,
> id, day
> x,300
> x,250,
> x,0
> y,250
> y,4
>
> I only want the records
> 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)
>
Try,
SELECT min(day),id from reg group by id;
min | id
-----+----
4 | y
0 | x
(2 rows)
> Thanks in advance
>
>
>
>
> __________________________________
> Do you Yahoo!?
> New and Improved Yahoo! Mail - 100MB free storage!
> http://promotions.yahoo.com/new_mail
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
--
-Achilleus
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: only last records in subgroups
@ 2004-08-23 04:09 Greg Stark <gsstark@mit.edu>
parent: Dino Vliet <dino_vliet@yahoo.com>
1 sibling, 0 replies; 3+ messages in thread
From: Greg Stark @ 2004-08-23 04:09 UTC (permalink / raw)
To: Dino Vliet <dino_vliet@yahoo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-08-23 04:09 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-17 10:44 only last records in subgroups Dino Vliet <dino_vliet@yahoo.com>
2004-08-17 11:35 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-08-23 04:09 ` 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