June 11, 2010 at 11:15 am
I need to select data from an existing table where a row may be duplicated but I want to select just one instance of each duplicated row based upon the value of one of its fields.
For example:
USE tempdb
GO
CREATE TABLE #Temp
(
product CHAR(10),
description CHAR(100),
price NUMERIC(18,9)
)
INSERT INTO #Temp
SELECT 'A', 'Test Prod A', 10
UNION ALL
SELECT 'B', 'Test Prod B', 10
UNION ALL
SELECT 'C', 'Test Prod C', 10
UNION ALL
SELECT 'A', 'Test_Prod A', 0
SELECT *
FROM #Temp
DROP TABLE #Temp
In this example the description for product A has been specified differently, I would want to extract the row that has the highest price. Unfortunately I do not have control over data entering the base table so cannot prevent these errors from happening.
June 11, 2010 at 11:40 am
SELECT product, MAX(description) AS description, max(price) AS price
FOM table
GROUP BY product
--ORDER BY product
Scott Pletcher, SQL Server MVP 2008-2010
June 11, 2010 at 11:46 am
I'd go with a TOP (1) and an ORDER BY on the value. You'll get exactly what you want.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 11, 2010 at 11:50 am
Thanks Scott
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply