Re: distinct on
From: Thomas Kellerer <thomas.kellerer_at_mgm-tp.com>
Date: Thu, 18 Oct 2012 08:42:11 +0200
Message-ID: <507FA4C3.7030404_at_mgm-tp.com>
jo, 18.10.2012 08:38:
> I found an interesting non standard feature in postgres to distinguish
> one column in a query.
> How can I do this in Oracle?
>
> select distinct on(a) * from test;
> a | b
> ----+-----
> a | bb
> aa | bbb
> ab | bbb
> (3 rows)
>
Date: Thu, 18 Oct 2012 08:42:11 +0200
Message-ID: <507FA4C3.7030404_at_mgm-tp.com>
jo, 18.10.2012 08:38:
> I found an interesting non standard feature in postgres to distinguish
> one column in a query.
> How can I do this in Oracle?
>
> select distinct on(a) * from test;
> a | b
> ----+-----
> a | bb
> aa | bbb
> ab | bbb
> (3 rows)
>
select a,b
from (
select a,b,row_number() over (partition by a order by b) as rn
from test
) t
where rn = 1
(that's actually ANSI standard SQL, would work in PostgreSQL as well)
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Oct 18 2012 - 08:42:11 CEST