Home » SQL & PL/SQL » SQL & PL/SQL » How to get top X record (PL/SQL Release 10.2.0.4.0 - Production)
How to get top X record [message #359069] Thu, 13 November 2008 11:42 Go to next message
vesile_taskiran
Messages: 66
Registered: August 2008
Location: Turkey
Member
i have a table like

id value
1 5
1 4
1 3
1 6
1 5
1 3
1 7
1 2
2 1
2 5
2 7
2 4
2 2
2 8
2 6
2 8

i will take first three records of id 1 and first 2 record of id 2 and average them etc. result will be like

id value
1 4
2 3

how can I do it with query?

Regards.
Re: How to get top X record [message #359072 is a reply to message #359069] Thu, 13 November 2008 11:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for "Top n" or the like.
This has been asked and answered many times.

Always put a test case: create table and insert statements
Always put what you already tried
Always search BEFORE posting
In short, always read OraFAQ Forum Guide

Regards
Michel

[Updated on: Thu, 13 November 2008 11:55]

Report message to a moderator

Re: How to get top X record [message #359149 is a reply to message #359072] Thu, 13 November 2008 20:57 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi

Quote:


first three records of id 1 and first 2 record of id 2



how many ids and how many rows (both are varying) will be taken by vesile_taskiran is not known

as Michel suggests

Quote:
TOP n


is it possible in single select? I am also curious
yours
dr.s.raghunathan
Re: How to get top X record [message #359152 is a reply to message #359069] Thu, 13 November 2008 21:34 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Quote:
i will take first three records of id 1 and first 2 record of id 2 and average them etc


Then how will you get the result as below ?

Quote:
id value
1 4
2 3


Smile
Rajuvan.
Re: How to get top X record [message #359174 is a reply to message #359069] Fri, 14 November 2008 00:28 Go to previous messageGo to next message
vesile_taskiran
Messages: 66
Registered: August 2008
Location: Turkey
Member
 
create table table_x
(
id number, 
value_x number
);
insert into table_x(id,value_x)
values (1,5);
insert into table_x(id,value_x)
values (1,3);
insert into table_x(id,value_x)
values (1,5);
insert into table_x(id,value_x)
values (1,4);
insert into table_x(id,value_x)
values (1,2);
insert into table_x(id,value_x)
values (2,3);
insert into table_x(id,value_x)
values (2,5);
insert into table_x(id,value_x)
values (2,1);
insert into table_x(id,value_x)
values (2,8);
insert into table_x(id,value_x)
values (2,5);
insert into table_x(id,value_x)
values (2,2);
insert into table_x(id,value_x)
values (2,1);

select id,avg(value_x)
from table_x
where rownum<get_record_count_for_id(id)

for example i have above records on table_x get_record_count_for_id(1)=2
get_record_count_for_id(2)=3
and result should be 4 for id=1 and  3 for id=2


in this query if i have only 1 id it will work fine but i have more id's.I will average topn for all id's.
Re: How to get top X record [message #359181 is a reply to message #359174] Fri, 14 November 2008 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the code of "get_record_count_for_id(id)"?

Have a look at ROW_NUMBER function.

Regards
Michel
Re: How to get top X record [message #359189 is a reply to message #359174] Fri, 14 November 2008 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And also define "first" (that is define the order of the rows).

Regards
Michel
Re: How to get top X record [message #359248 is a reply to message #359189] Fri, 14 November 2008 06:03 Go to previous messageGo to next message
vesile_taskiran
Messages: 66
Registered: August 2008
Location: Turkey
Member
get_record_count_for_id(id)
returns number 2 or 3 or something.(number of cells under each id)
for example for the
first id i should get records between 1 and 2 (1,2) and average them
second id i should get records between 7 and 9 (7,8,9) and average them
oracle has function
 max(id) keep (dense_rank last order by value) 

which takes biggest value for each id. i need something like that
but i need biggest n values for each id. Please help me Sad
Re: How to get top X record [message #359251 is a reply to message #359248] Fri, 14 November 2008 06:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your first example which should be taken in each id and why?

Regards
Michel

[Updated on: Fri, 14 November 2008 06:21]

Report message to a moderator

Re: How to get top X record [message #359266 is a reply to message #359251] Fri, 14 November 2008 07:59 Go to previous messageGo to next message
vesile_taskiran
Messages: 66
Registered: August 2008
Location: Turkey
Member
i ll explain you the real problem. this is a telecomunication project. I have a city table and each city has cells and call_traffic like

city_name, cell_name, call_traffic

i order the cities by lowest traffic and i find a result from worst to best.
i need to average traffic values for each city but i ll take the worst 10% cells.

 
city A has 1000 cells and i average 100 worst of them 
city B has 500 cells and i average 50 worst of them 
city C has 800 cells and i average 80 worst of them
at the end each city has a %10 worst call_traffic average
"get_record_count_for_id(id) function returns me %10 like 100,50,80 etc."


Can i do that with a select statement? there should be something like Keep dense_rank but what i dont know?
Re: How to get top X record [message #359267 is a reply to message #359266] Fri, 14 November 2008 08:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is far most understandable but one point, what does "get_record_count_for_id(id) function returns me %10 like 100,50,80 etc." mean? Does it return you 1/10 of the number of rows for the given id?

Regards
Michel
Re: How to get top X record [message #359291 is a reply to message #359267] Fri, 14 November 2008 10:27 Go to previous messageGo to next message
vesile_taskiran
Messages: 66
Registered: August 2008
Location: Turkey
Member
yes it returns cell_count/10 if cell_count=500 it returns 50 which means first 50 records will be aggregated.
(sorry for my english)
Re: How to get top X record [message #359301 is a reply to message #359291] Fri, 14 November 2008 11:09 Go to previous message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With your data in first post:
SQL> with 
  2    data as (
  3      select id, v, 
  4              row_number() over (partition by id order by v) rn,
  5              round(count(*) over (partition by id)/10) cnt
  6      from t
  7    )
  8  select id, avg(v) average, cnt nb_values
  9  from data
 10  where rn <= cnt
 11  group by id, cnt
 12  order by 1
 13  /
        ID    AVERAGE  NB_VALUES
---------- ---------- ----------
         1          2          1
         2          1          1

2 rows selected.

SQL> desc t
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 ID                                        NUMBER(38)
 V                                         NUMBER(38)

Regards
Michel

[Updated on: Fri, 14 November 2008 11:12]

Report message to a moderator

Previous Topic: CONNECT BY NOCYCLE gives different results
Next Topic: How to build a function in order to be called in SQL
Goto Forum:
  


Current Time: Thu Dec 08 04:11:31 CST 2016

Total time taken to generate the page: 0.16047 seconds