OraFAQ Forum: SQL & PL/SQL » Query For N number to top Sales in Region

Home » SQL & PL/SQL » SQL & PL/SQL » Query For N number to top Sales in Region (oracle 10 g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query For N number to top Sales in Region [message #658111]

Sun, 04 December 2016 23:55

annu-agi
Messages: 238
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

Report message to a moderator

Re: Query For N number to top Sales in Region [message #658112 is a reply to message #658111]

Mon, 05 December 2016 00:12

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

How does one select the TOP N rows from a table?

Report message to a moderator

Re: Query For N number to top Sales in Region [message #658113 is a reply to message #658111]

Mon, 05 December 2016 00:19

Michel Cadot
Messages: 68637
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.

Report message to a moderator

Re: Query For N number to top Sales in Region [message #658114 is a reply to message #658113]

Mon, 05 December 2016 00:32

annu-agi
Messages: 238
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);

Report message to a moderator

Re: Query For N number to top Sales in Region [message #658115 is a reply to message #658114]

Mon, 05 December 2016 00:39

Michel Cadot
Messages: 68637
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

annu-agi
Messages: 238
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

Report message to a moderator

Re: Query For N number to top Sales in Region [message #658118 is a reply to message #658117]

Mon, 05 December 2016 01:18

Michel Cadot
Messages: 68637
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 14:01:37 CDT 2024