Re: How to select a random row from each sub table after "group by"?

From: yossarian <yossarian99_at_operamail.com>
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

Original text of this message