DECLARE @Sample TABLE(Product VARCHAR(20), Price_Selling INT, Price_Selling_After_Discount
INT, Price_Selling_With_Tax_or_Incentive INT)
INSERT INTO @Sample SELECT
'Apple', 5, 3, 3 UNION ALL SELECT
'Orange', 4, 5, 6 UNION ALL SELECT
'Watermelon', 7,
8, 9 UNION ALL SELECT
'Banana', 10, 11, 9;
|
Query
SELECT
Product
, Price_Min_of_All = (SELECT MIN(Price)
FROM (SELECT Price_Selling UNION
SELECT Price_Selling_After_Discount UNION
SELECT Price_Selling_With_Tax_or_Incentive)Price(Price))
FROM @Sample
|
Also can written as
SELECT
Product
, MIN(Price_Selling)
FROM (
SELECT
Product
, Price_Selling
FROM @Sample
UNION
SELECT
Product
, Price_Selling_After_Discount
FROM @Sample
UNION
SELECT
Product
, Price_Selling_With_Tax_or_Incentive
FROM @Sample
)Src
GROUP BY Product
|
SELECT
Price.*
FROM @Sample
CROSS APPLY (
SELECT MIN(Price)
FROM (
VALUES (Price_Selling),
(Price_Selling_After_Discount),
(Price_Selling_With_Tax_or_Incentive)
) AS Price(Price)
) AS Price(Price);
|
Desired output
No comments:
Post a Comment