I am trying to pull in data that is duplicate in my table to flush out where the duplicates are. I have a statement that works and will get me my duplicates, but I cannot pull in the additional data I need. My query is:
SELECT udvalue, udjoin FROM udf WHERE udtype = 'MT' and udfindex = '221' and udjoin like '5%' GROUP BY udvalue HAVING COUNT(udvalue) > 1 ORDER BY udvalue
If I remove the udjoin in the select statement it works fine, but I also want to have the udjoin value that corresponds to the udvalue that is duplicate so I know where to make my changes. If I place the udjoin in the GROUP BY clause, I get no data. How can I get the udjoin value to show up?
SELECT u.*
FROM udf u
INNER JOIN
(
SELECT udvalue
FROM udf
WHERE udtype = 'MT' and udfindex = '221' and udjoin like '5%'
GROUP BY udvalue
HAVING COUNT(udvalue) > 1
)u1
ON u1.udvalue = u.udvalue
ORDER BY u.udvalue
why are you naming a table as udf by the way?
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
nope...it just fetched data from existing table udf. u is just a short name given for table called alias. Though here its not pronounced, using aliases will make sure you dont have to repeat table names everywhere and also clearly indicates which column comes from which table
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
GROUP BY function
Posted - 04/19/2012 : 14:36:36
