| From: | Manfred Koizar <mkoi-pg(at)aon(dot)at> |
|---|---|
| To: | "Albrecht Berger" <berger1517(at)gmx(dot)ch> |
| Cc: | "pgsql" <pgsql-sql(at)postgresql(dot)org> |
| Subject: | Re: sql group by statement |
| Date: | 2002-09-13 12:16:02 |
| Message-ID: | g2l3ou44jver0jvic4477cd66d0k92c6li@4ax.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-sql |
On Fri, 13 Sep 2002 12:29:21 +0200, "Albrecht Berger"
<berger1517(at)gmx(dot)ch> wrote:
>Table :
>pk id val1 val2
> 1 1 2 3
> 2 1 2 4
> 3 2 1 1
> 4 1 0 5
> 5 2 1 8
>
>
>Needed Result :
>pk id val1 val2
> 4 1 0 5
> 5 2 1 8
Albrecht,
"DISTINCT ON eliminates rows that match on all the specified
expressions, keeping only the first row of each set of duplicates."
So the trick is to sort appropriately:
SELECT DISTINCT on (id) pk, id, val1, val2
FROM yourtable
ORDER BY id asc, val2 desc, pk desc;
Servus
Manfred
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Manfred Koizar | 2002-09-13 12:46:05 | Re: Table alias in DELETE statements |
| Previous Message | Hanno Wiegard | 2002-09-13 12:10:25 | Table alias in DELETE statements |