Home » SQL & PL/SQL » SQL & PL/SQL » Query For N number to top Sales in Region (oracle 10 g)
|
|
|
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);
|
|
|
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: 68693 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.
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
|
|
|
|
Goto Forum:
Current Time: Fri Sep 27 23:42:15 CDT 2024
|