Re: Sql question with rownum!

> Hi all,
>
> I have a little question in Sql :
>
> I want to use the rownum pseudocolumn to count the records in a
> select statement with a group by.... Is it possible, if yes, how i do
> that.
>
> The following tables/result represent an example of what i want.
>
> After the 1st Query (The one that i group by) , i get the result:
>
> ColumnName Count(*)
> ========= ======
>
> Data1 2
> Data2 15
>
> The following table represent what i want:
>
> RowNum ColumnName
> ====== =========
> 1 Data1
> 2 Data1
> 1 Data2
> 2 Data2
> 3 Data2
> 4 Data2
> ... Data2
> 15 Data2
>
> Thanks in advance for your help

Do analytic functions help?

set feedback off
set pages 4000

create table rn (

   columnname varchar2(30)
);

insert into rn values ('Data2');
insert into rn values ('Data2');
insert into rn values ('Data2');
insert into rn values ('Data2');
insert into rn values ('Data2');
insert into rn values ('Data2');
insert into rn values ('Data2');
insert into rn values ('Data2');
insert into rn values ('Data2');
insert into rn values ('Data2');
insert into rn values ('Data2');
insert into rn values ('Data1');
insert into rn values ('Data2');
insert into rn values ('Data2');
insert into rn values ('Data2');
insert into rn values ('Data2');
insert into rn values ('Data2');
insert into rn values ('Data1');
insert into rn values ('Data2');

select count(*), columnname from rn group by columnname;

select rank() over(partition by columnname order by rownum) r, columnname from rn;

drop table rn;  

Rene

-- 
Recherchen im schweizerischen Handelsregister: 
http://www.adp-gmbh.ch/SwissCompanies/Search.php3