This was an interesting SQL concept i came across to check for the Distinct of records and get a Distinct A,B,C and also to get the Distinct (B), from the result of distinct combination. I am sure i have confused you!!

OK.. let me put this in a more simple form, We derive at combination of distinct Product, Price, Size. This shall give a result something as below,

And to get the same scenario in my sample table i query into the Product table, taking only a single product which gives all the distinct combinations as below (taken only one product for instance here),

But what if i need the one combination to get the one with the maximum price, which can be attained using the Row-Number() function as in my sample query,

SELECT ProductID,ReferenceOrderLineID,ReferenceOrderID, ActualCost
FROM
(SELECT Production.TransactionHistory.*,
ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ActualCost DESC) AS RN
FROM Production.TransactionHistory) AS t
WHERE RN = 1 

And here we go with the distinct and only transaction of every product with the maximum cost as is shown below,

So we can use the same where the Distinct and group by clause might be help less. Thanks for a read, share and enjoy 🙂

Advertisements
%d bloggers like this: