GROUP BY function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

GROUP BY function

Author

Topic

Tototo1
Starting Member

22 Posts

Posted - 2012-04-19 : 14:36:36

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?

Thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 14:42:41

[code]
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
[/code]

why are you naming a table as udf by the way?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Tototo1
Starting Member

22 Posts

Posted - 2012-04-19 : 14:48:20

That is just the table name that is in the system I am currently using!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 14:50:31

ok....not a good convention to name table like that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Tototo1
Starting Member

22 Posts

Posted - 2012-04-19 : 14:53:28

Works beautifully. Thank you. I agree but I have no choice in the matter unfortunately!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 14:54:22

no problem..its just a tip you can consider while you do object designs in future

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Tototo1
Starting Member

22 Posts

Posted - 2012-04-19 : 14:58:30

If you don;t mind me asking, does doing the

SELECT u.*
FROM udf u

create a temp table u? I guess I have never seen a select on something that didn't exist before the FROM statement. (I'm kinda new at this)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 15:25:49

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources