how to get min and max value from same column with no primary id

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

SQL

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.

SQL

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

Posted 14-Oct-15 11:38am

Nandhini Devi

Updated 14-Oct-15 20:19pm

v4

Add a Solution

Comments

ZurdoDev 14-Oct-15 19:49pm

If you can get for state, why can't you get for province?

Nandhini Devi 14-Oct-15 21:27pm

I am not getting max sale for province .

ZurdoDev 15-Oct-15 7:05am

Why? You didn't answer my question.

[no name] 15-Oct-15 1:24am

Do you need output like below format. Please confirm:

Province MAX Sales MIN Sales
-------- ---------- ----------
Beijing 70000 10000
Delhi 80000 5000

Nandhini Devi 15-Oct-15 1:41am

Country MaxSales MaxSaleprovince MinSales MinSaleprovince
======= ======== =============== ========= ===============
CHINA 70000 beiging 10000 beijing
India 80000 delhi 20000 tamilnadu

[no name] 15-Oct-15 2:01am

Let' take country as 'China".

It has 3 transactions 70000(Beijing), 30000(Sanghai), 10000(Beinjing). MaxSales Province value will be Beijing, that is fine. But for MinSales Province how come Sanghai will come.. please explain your logic..

Nandhini Devi 15-Oct-15 2:20am

its just a sample table I gave

[no name] 15-Oct-15 2:24am

That's ok. But what should be correct value for MINProvince, MaxProvince as per your question.

Nandhini Devi 15-Oct-15 2:46am

I have edited my question sir.

[no name] 15-Oct-15 3:08am

SELECT ST.Country, MAX(ST.Sales) MaxSales,
(SELECT TOP 1 Province FROM SaleTable ST2
WHERE ST2.Country = ST.Country AND ST2.Sales = MAX(ST.Sales)) MaxSaleprovince,
MIN(ST.Sales) MinSales,
(SELECT TOP 1 Province FROM SaleTable ST2
WHERE ST2.Country = ST.Country AND ST2.Sales = MIN(ST.Sales)) MinSaleprovince
FROM SaleTable ST
GROUP BY ST.Country

Nandhini Devi 15-Oct-15 15:09pm

both answers are right

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Corporal Agarn · Accepted Answer · 2015-10-15T06:55:00

Here is what I came up with:

SQL

WITH cte1 AS (
    SELECT s.Country, s.Province, s.Sales
    , ROW_NUMBER() OVER(PARTITION BY s.Country ORDER BY s.Sales) AS RMX
    , ROW_NUMBER() OVER(PARTITION BY s.Country ORDER BY s.Sales DESC) AS RMN
    from #SaleTable s
)
SELECT c1.Country, 
    c1.Province AS ProvinceMin, c1.Sales AS SalesMin, 
    c2.Province AS ProvinceMax, c2.Sales AS SalesMax
from cte1 c1
INNER JOIN cte1 c2 ON c1.Country = c2.Country AND c2.RMX = 1
WHERE c1.RMN = 1;