From: | "Octavio Alvarez" <alvarezp(at)octavio(dot)ods(dot)org> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | MSAccess-like Last() with sorting before grouping |
Date: | 2003-05-15 00:50:04 |
Message-ID: | 4793.63.84.67.3.1052959804.squirrel@doogie.ods.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hello! This is my first post to the list. I hope I'm not just blanked out,
and asking a question with a simple answer. ;-)
I need a query to return the last value of a set of grouped-by records
when a column is sorted. For example:
TABLE: (
id integer UNIQUE,
group_key integer,
sort_key integer,
data integer
)
Say it has the following rows:
id | group_key | sort_key | data
----+-----------+----------+------
1 | 1 | 1 | 1
2 | 1 | 2 | 6
3 | 1 | 5 | 6
4 | 1 | 9 | 2
5 | 2 | 3 | 1
6 | 2 | 4 | 3
7 | 2 | 7 | 3
8 | 3 | 6 | 5
9 | 3 | 3 | 4
(9 rows)
I need it to have the following output:
id | group_key | sort_key | data
----+-----------+----------+------
4 | 1 | 9 | 2
7 | 2 | 7 | 3
8 | 3 | 6 | 5
Which is, selecting the top value from sort_key from each different set of
group_key. I can't program a last() function because it wouldn't work for
group_key=3 in the example. ORDER BY always sorts the results after the
grouping. I tried min/max functions but they apply to each column
individually.
Is there any way I can ask for this info to the SQL server?
Thank you very much.
--
Octavio Alvarez Piza.
E-mail: alvarezp(at)octavio(dot)ods(dot)org
From | Date | Subject | |
---|---|---|---|
Next Message | Dani Oderbolz | 2003-05-15 09:18:55 | Re: MD5 encryption, Was: Multilingual database |
Previous Message | Tom Lane | 2003-05-14 21:34:55 | Re: can't alter user with the postgres user |