MSAccess-like Last() with sorting before grouping

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

Responses

Browse pgsql-novice by date

  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