Creating Table from Select and Group By

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

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Creating Table from Select and Group By

Author

Topic

vwilsonjr
Starting Member

45 Posts

Posted - 2003-04-11 : 09:10:59

Sorry I posted this in the wrong forum.

I'm trying to create a table of duplicates I have in my database. This is the code

SELECT Employee_Number
As Tmp GROUP BY [Employee_Number] HAVING Count(*)>1
INTO #a
FROM [Appuser]

If I leave off everything behind Group By it gives me all of the Employee_numbers. I only want the ones duplicated. Then I want to join that table to the employee table to display all of other field in the employee table.

Edited by - vwilsonjr on 04/11/2003 09:29:07

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-04-11 : 09:45:55

To display all the duplicated records
SELECT * FROM [APPUSER] WHERE EMPLOYEE_NUMBER IN
(
SELECT EMPLOYEE_NUMBER FROM [APPSUER]
GROUP BY EMPLOYEE_NUMBER HAVING COUNT(*)>1
)

To display distinct of all the duplicated records
SELECT Distinct col1,col2 FROM [APPUSER] WHERE EMPLOYEE_NUMBER IN
(
SELECT EMPLOYEE_NUMBER FROM [APPSUER]
GROUP BY EMPLOYEE_NUMBER HAVING COUNT(*)>1
)

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources