CODEWITHSUNDEEP
t-sql
Raphael
Raphael

Reputation: 853

select max top 1 value by group of values

Using code sample below, how can I get the result set from @ExpectedResult table?

What is required is to group by DFH_Link and Famille_Code to find the Discount value with the max Occurence value.

DECLARE @Data TABLE
(
    DFH_Link VARCHAR(25), 
    Famille_Code VARCHAR(25),
    Discount DECIMAL(8,3), 
    Occurence INT
)

INSERT INTO @Data (DFH_Link, Famille_Code, Discount, Occurence) VALUES('Link 1', 'Family 1', 10, 2)
INSERT INTO @Data (DFH_Link, Famille_Code, Discount, Occurence) VALUES('Link 1', 'Family 1', 15, 5)
INSERT INTO @Data (DFH_Link, Famille_Code, Discount, Occurence) VALUES('Link 1', 'Family 1', 25, 3)
INSERT INTO @Data (DFH_Link, Famille_Code, Discount, Occurence) VALUES('Link 1', 'Family 2', 12, 10)
INSERT INTO @Data (DFH_Link, Famille_Code, Discount, Occurence) VALUES('Link 1', 'Family 2', 45, 7)
INSERT INTO @Data (DFH_Link, Famille_Code, Discount, Occurence) VALUES('Link 1', 'Family 2', 60, 25)
INSERT INTO @Data (DFH_Link, Famille_Code, Discount, Occurence) VALUES('Link 2', 'Family 1', 10, 8)
INSERT INTO @Data (DFH_Link, Famille_Code, Discount, Occurence) VALUES('Link 2', 'Family 1', 18, 12)
INSERT INTO @Data (DFH_Link, Famille_Code, Discount, Occurence) VALUES('Link 2', 'Family 1', 25, 3)
INSERT INTO @Data (DFH_Link, Famille_Code, Discount, Occurence) VALUES('Link 2', 'Family 2', 12, 18)
INSERT INTO @Data (DFH_Link, Famille_Code, Discount, Occurence) VALUES('Link 2', 'Family 2', 45, 37)
INSERT INTO @Data (DFH_Link, Famille_Code, Discount, Occurence) VALUES('Link 2', 'Family 2', 60, 20)

DECLARE @ExpectedResult TABLE
(
    DFH_Link VARCHAR(25), 
    Famille_Code VARCHAR(25),
    Discount DECIMAL(8,3)
)

INSERT INTO @ExpectedResult (DFH_Link, Famille_Code, Discount) VALUES ('Link 1', 'Family 1', 15)
INSERT INTO @ExpectedResult (DFH_Link, Famille_Code, Discount) VALUES ('Link 1', 'Family 2', 60)
INSERT INTO @ExpectedResult (DFH_Link, Famille_Code, Discount) VALUES ('Link 2', 'Family 1', 18)
INSERT INTO @ExpectedResult (DFH_Link, Famille_Code, Discount) VALUES ('Link 2', 'Family 2', 45)

SELECT * FROM @Data
SELECT * FROM @ExpectedResult

Upvotes: 1

Views: 28

Answers (1)

JMabee
JMabee

Reputation: 2300

You can just use a window function to group the data by Link and Famille and then just pull out the one with the highest occurance:

SELECT DFH_Link, Famille_Code, Discount  
FROM (
    SELECT DFH_Link, Famille_Code, Discount, Occurence, ROW_NUMBER() OVER(Partition by DFH_Link,Famille_Code ORDER BY Occurence desc) RN
    from @Data) t1
WHERE RN = 1

Upvotes: 2

Related Questions