Re: How to select a random row from each sub table after "group by"?
Date: Thu, 04 Jun 2009 11:23:38 +0200
Message-ID: <4a27929a$0$709$5fc30a8_at_news.tiscali.it>
Phper wrote:
> After "group by", the big table is grouped into many sub tables. I
> want to select a random row from each sub table. That's what I want.
> How can I realize/implement it?
I don't know if I have understood correctly what you ask, anyway here's my 2 cents:
hr_at_> create table x ( class varchar2(80), data1 integer, data2 integer ) ;
Table created.
hr_at_> insert into x values ('A',42,24);
1 row created.
hr_at_> insert into x values ('A',138,23);
1 row created.
hr_at_> insert into x values ('A',0,84);
1 row created.
hr_at_> insert into x values ('B',61,29);
1 row created.
hr_at_> insert into x values ('B',31,123);
1 row created.
hr_at_> insert into x values ('B',91,106);
1 row created.
hr_at_> insert into x values ('C',55,99);
1 row created.
hr_at_> insert into x values ('C',80,66);
1 row created.
hr_at_> insert into x values ('C',29,18);
1 row created.
hr_at_> select
2 class,
3 data1,
4 data2
5 from
6 (
7 select 8 class, 9 data1, 10 data2, 11 dense_rank() over (partition by class order by dbms_random.random) rnk 12 from 13 x
14 )
15 where
16 rnk = 1
17* ; Received on Thu Jun 04 2009 - 04:23:38 CDT