Re: Ask About SQL

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Ask About SQL
Date: 2009-08-19 10:46:48
Message-ID: 20090819104648.GA11859@a-kretschmer.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

In response to Otniel Michael :
> Hi All.
>
> Can help to create sql queries for this data :
>
> tabel A
> field1, field2, field3
> x1,     y1,     5
> x1,     y2,     1
> x2,     y1,     2
> x2,     y3,     4
> x1,     y3,     4
>
> I want to get 2 record with the max value at field3 for each kombination of
> field1 :
>
> tabel B
> field1, field2, field3
> x1,     y1,     5
> x1,     y3,     4
> x2,     y3,     4
> x2,     y1,     2
>
> Anyone have an ideas?

Works since 8.4:

test=*# select * from table_a ;
field1 | field2 | field3
--------+--------+--------
x1 | y1 | 5
x1 | y2 | 1
x2 | y1 | 2
x2 | y3 | 4
x1 | y3 | 4
(5 rows)

test=*# select field1, field2, field3 from (select field1, field2,
field3, row_number() over(partition by field1 order by field3 desc) from
table_a order by field1, field3) foo where row_number < 3 order by
field1, field2;
field1 | field2 | field3
--------+--------+--------
x1 | y1 | 5
x1 | y3 | 4
x2 | y1 | 2
x2 | y3 | 4
(4 rows)

Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Joshua Tolley 2009-08-19 14:00:40 Re: Ask About SQL
Previous Message Ivan Voras 2009-08-19 10:19:17 Re: SQL Query Performance - what gives?