CREATE TABLE dim.SaleTable
([Year] INTEGER,
Country VARCHAR(50),
Province VARCHAR(50),
Sales INTEGER
);
INSERT INTO dim.SaleTable([year],Country,Province,Sales)
VALUES (2008,'USA','NJ',10000),
(2008,'INDIA','DELHI',5000),
(2008,'USA','WA',40000),
(2008,'CHINA','Beijing',10000),
(2009,'CHINA','Shanghai',30000),
(2009,'UK','London',10000),
(2009,'INDIA','TamilNadu',20000),
(2009,'USA','NJ',30000),
(2010,'UK','London',60000),
(2011,'CHINA','Beijing',70000),
(2011,'INDIA','Delhi',80000),
(2011,'FRANCE','Mende',50000),
(2011,'USA','NY',60000)<pre lang="SQL">
This is the table and I want to get min and max sales with max sales province and min sales province from this table. I can get max and min sales but for min value of state and max sales of state I am not getting.
SELECT Rank1,MAX(Sales) AS MAXSales,MIN(Sales) AS MinSales,Country
FROM (SELECT Country, Province,sales,RANK() OVER (ORDER BY Country) AS Rank1 FROM dim.SaleTable GROUP BY Country,Province,Sales)d
GRoup BY Rank1,Country
Country MaxSales MaxSaleprovince MinSales MinSaleprovince
======= ======== =============== ========= ===============
CHINA 70000 beiging 10000 beijing
India 80000 delhi 20000 tamilnadu