Re: ORACLE SQL QUERY Help!!!

<yilmaz.celik_at_gmail.com> a écrit dans le message de news: 1150503662.651772.322520_at_p79g2000cwp.googlegroups.com...
| Hi,
|
| I am new to oracle plsql thing.
|
| I have one table X like this. I want to get X amount of rows UNDER
| each group.
|
| type name value
| ---------- ---------- ----------
| 1 a 1
| 1 a 2
| 1 a 3
| 1 b 4
| 1 b 4
| 1 b 4
| 1 b 3
| 1 b 3
| 1 b 2
| 1 b 5
| 1 c 3
| 1 c 5
| 1 c 6
| 1 c 7
| 1 c 7
| 1 d 1
| 1 d 2
| 2 e 1
| 2 e 2
| 2 e 2
| 2 e 3
| 2 f 23
| 2 f 3
| 2 f 4
| 2 f 2
| 2 g 4
| 2 g 2
|
|
|
| I tried this:
|
| select type,name,count(name),avg(value)
|
| from x
|
| group by type,name having count(name) > 3
|
| order by count(name)
|
|
| And the result is
|
| type name count(name) avg(value)
| ---------- ---------- ---------- ----------
| 1 b 6 3.57
| 1 c 5 5.6
| 1 a 3 2
| 2 e 4 2
| 2 f 4 8
|
| The result is ok but my problem is : I WANT TO SEE ONLY 2 ROWS UNDER
| EACH TYPE. I try to do something with ROWNUM , but its X number of
| giving the rows begining from the top. but i want to see only 2rows
| undeR THE GROUP OF type.
|
| PRINTOUT I WANT TO GET IS THIS:
|
| type name count(name) avg(value)
| ---------- ---------- ---------- ----------
| 1 b 6 3.57
| 1 c 5 5.6
| 2 e 4 2
| 2 f 4 8
|
|
| CRITERIA: TOP 2 NAME UNDER EACH GROUP(TYPE) WHO HAS COUNT(NAME) >2
|
|
| iF YOU CAN HELP ME I REALLY APPRECIATE IT.
| THANKS A LOT IN ADVANCE.
|

Have a look at row_number, rank and dense_rank functions and pick the one that best fit your requirements.

Regards
Michel Cadot Received on Sat Jun 17 2006 - 01:24:34 CDT