On 9/12/2011 9:54 PM, Reid Thompson wrote:
Ack -- i flubbed the subject and sample.
The sample data should be
val val2 date
1 1 2011-01-01
2 2 2011-01-02
3 3 2011-01-03
4 1 2011-01-04
5 2 2011-01-05
5 3 2011-01-01
4 1 2011-01-02
6 2 2011-01-03
4 3 2011-01-04
3 1 2011-01-05
2 2 2011-01-06
4 3 2011-01-07
6 1 2011-01-08
4 2 2011-01-09
5 3 2011-01-01
2 1 2011-01-02
4 2 2011-01-03
2 3 2011-01-04
1 1 2011-01-01
2 2 2011-01-02
3 3 2011-01-03
4 1 2011-01-04
3 2 2011-01-05
1 3 2011-01-01
2 1 2011-01-02
3 2 2011-01-03
4 3 2011-01-04
5 4 2012-01-01
resultset:
1 3 2011-01-01
2 2 2011-01-06
3 1 2011-01-05
4 2 2011-01-09
5 2 2011-01-05
6 1 2011-01-08
where any one of these 3
1 1 2011-01-01
1 1 2011-01-01
1 3 2011-01-01
or any one of these 2
3 1 2011-01-05
3 2 2011-01-05
are suitable for val = 1, val = 3 respectively.
sigh -- looks like I fat fingered one of my resultset values above.
But, I think this gives me what I want:
test=# select distinct on (val1) val1, val2, val3 from (SELECT
max(val3) OVER (PARTITION BY val1), * FROM sampledata) as sq where val3
= max order by val1;
val1 | val2 | val3
------+------+------------
1 | 3 | 2011-01-01
2 | 2 | 2011-01-06
3 | 2 | 2011-01-05
4 | 2 | 2011-01-09
5 | 4 | 2012-01-01
6 | 1 | 2011-01-08
(6 rows)
val1 | val2 | val3
------+------+------------
1 | 3 | 2011-01-01
1 | 1 | 2011-01-01
1 | 1 | 2011-01-01
2 | 2 | 2011-01-02
2 | 1 | 2011-01-02
2 | 3 | 2011-01-04
2 | 1 | 2011-01-02
2 | 2 | 2011-01-06
2 | 2 | 2011-01-02
3 | 3 | 2011-01-03
3 | 3 | 2011-01-03
3 | 2 | 2011-01-05
3 | 1 | 2011-01-05
3 | 2 | 2011-01-03
4 | 1 | 2011-01-04
4 | 1 | 2011-01-02
4 | 3 | 2011-01-04
4 | 3 | 2011-01-07
4 | 3 | 2011-01-04
4 | 2 | 2011-01-09
4 | 1 | 2011-01-04
4 | 2 | 2011-01-03
5 | 4 | 2012-01-01
5 | 2 | 2011-01-05
5 | 3 | 2011-01-01
5 | 3 | 2011-01-01
6 | 1 | 2011-01-08
6 | 2 | 2011-01-03
(28 rows)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
