# 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