Home » SQL & PL/SQL » SQL & PL/SQL » Query For N number to top Sales in Region (oracle 10 g)
Query For N number to top Sales in Region [message #658111] Sun, 04 December 2016 23:55 Go to next message
annu-agi
Messages: 228
Registered: July 2005
Location: Karachi
Senior Member

Hi experts,

I have 3 columns region, customer and sum (of sales per customer in a region)
how can it drive regionwise top 3 sales in a region .

like
region 		customer		sales 

south 		fashion fab		150000
south 		living deliights 	9000
south 		creative kidz		7000
north		macdormat store		280000
north 		lolo stores		150000
north 		shabir the brand	6825
east		old navy 		3690000
east		zubaibas		568222
east		raimonds		259000

can anyone tell me how can i get this kind result


regards

anwer Ali
Re: Query For N number to top Sales in Region [message #658112 is a reply to message #658111] Mon, 05 December 2016 00:12 Go to previous messageGo to next message
Littlefoot
Messages: 21446
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How does one select the TOP N rows from a table?
Re: Query For N number to top Sales in Region [message #658113 is a reply to message #658111] Mon, 05 December 2016 00:19 Go to previous messageGo to next message
Michel Cadot
Messages: 65959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Also always post your Oracle version, with 4 decimals, as solution depends on it.

Re: Query For N number to top Sales in Region [message #658114 is a reply to message #658113] Mon, 05 December 2016 00:32 Go to previous messageGo to next message
annu-agi
Messages: 228
Registered: July 2005
Location: Karachi
Senior Member

MY database version is 10.1.2

create table scott.sales ( 
region varchar2(10),
customer varchar2(20),
sales number)
;

insert into scott.sales2(region, customer, sales,) values ('south ','fashion fab',150000);
insert into scott.sales2(region, customer, sales,) values ('south ','living deliights ',9000);
insert into scott.sales2(region, customer, sales,) values ('south ','creative kidz',7000);
insert into scott.sales2(region, customer, sales,) values ('north','macdormat store',280000);
insert into scott.sales2(region, customer, sales,) values ('north ','lolo stores',150000);
insert into scott.sales2(region, customer, sales,) values ('north ','shabir the brand',6825);
insert into scott.sales2(region, customer, sales,) values ('east','old navy ',3690000);
insert into scott.sales2(region, customer, sales,) values ('east','zubaibas',568222);
insert into scott.sales2(region, customer, sales,) values ('east','raimonds',259000);


Re: Query For N number to top Sales in Region [message #658115 is a reply to message #658114] Mon, 05 December 2016 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 65959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
MY database version is 10.1.2
I doubt this version ever existed.
SELECT * FROM V$VERSION;

Quote:
create table scott.sales
Quote:
insert into scott.sales2
Post a VALID test case.
Do NOT give schema, tablespace and storage parameters in your test case, we have not the same ones.

As you have only 3 rows per region then the top 3 in a region is just :
SELECT * FROM sales WHERE region = '<region>';

[Updated on: Mon, 05 December 2016 00:39]

Report message to a moderator

Re: Query For N number to top Sales in Region [message #658117 is a reply to message #658115] Mon, 05 December 2016 01:15 Go to previous messageGo to next message
annu-agi
Messages: 228
Registered: July 2005
Location: Karachi
Senior Member

thank you michal for rapid response

i need top 3 sales from every region like if you see the data once again, we have 3 regions south, north and east and lots of customers saling in these regions. now just i need every top 3 customer sales from every region means

region 1      top customer 1     9999999
region 1      top customer 2     8888888
region 1      top customer 3     7777777
region 2      top customer 1     7778888
region 2      top customer 2     6666666








functioned script for data
create table sales2 ( 
region varchar2(10),
customer varchar2(20),
sales number);

insert into sales2(region, customer, sales) values ('south ','fashion fab',150000);
insert into sales2(region, customer, sales) values ('south ','living deliights ',9000);
insert into sales2(region, customer, sales) values ('south ','creative kidz',7000);
insert into sales2(region, customer, sales) values ('north','macdormat store',280000);
insert into sales2(region, customer, sales) values ('north ','lolo stores',150000);
insert into sales2(region, customer, sales) values ('north ','shabir the brand',6825);
insert into sales2(region, customer, sales) values ('east','old navy ',3690000);
insert into sales2(region, customer, sales) values ('east','zubaibas',568222);
insert into sales2(region, customer, sales) values ('east','raimonds',259000);
insert into sales2(region, customer, sales) values ('south ','fashion fab99',10000);
insert into sales2(region, customer, sales) values ('south ','living dts ',90000);
insert into sales2(region, customer, sales) values ('south ','active koodz',17000);
insert into sales2(region, customer, sales) values ('north','macndo store',28000);
insert into sales2(region, customer, sales) values ('north ','honalolo stories',158090);
insert into sales2(region, customer, sales) values ('north ','the brand',786825);
insert into sales2(region, customer, sales) values ('east','blue old navy ',3690);
insert into sales2(region, customer, sales) values ('east','zubaibas collection ',5222);
insert into sales2(region, customer, sales) values ('east','raimond n raimonds',2599600);
commit;








db version is
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE	11.2.0.3.0	Production"
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production


Re: Query For N number to top Sales in Region [message #658118 is a reply to message #658117] Mon, 05 December 2016 01:18 Go to previous message
Michel Cadot
Messages: 65959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have to define what you want to do if some "sales" are equal.
Have a look at ROW_NUMBER, RANK and DENSE_RANK functions and at http://www.orafaq.com/forum/mv/msg/160920/472554/102589/#msg_472554 post.

[Updated on: Mon, 05 December 2016 01:19]

Report message to a moderator

Previous Topic: Element exists in a collection
Next Topic: materialize view logs rfresh
Goto Forum:
  


Current Time: Wed Nov 14 11:45:41 CST 2018