Display all fields including the field has dup

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

Display all fields including the field has dup

Author

Topic

Maachie
Yak Posting Veteran

69 Posts

Posted - 2007-11-14 : 17:23:21

I have one table where i want to use aggregate function for duplicate rows and also be able to select all fields to display. How would i do that?

Here is my query:

select Z, count(*)as num from Table
group by Z
having count(Z) > 1 ----- this returns 213 rows

Select Z, A,B,C,D From Table
Group By Z, A,B,C,D
Having Count(Z)>1 ----This gives me nothing

Hope I am explaining this correctly as to what I want.

Thanks,
Saru Brochu

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-14 : 17:26:29

Select t1.Z, t1.A, t1.B, t1.C, t1.D
From Table as t1
inner join (
select Z from Table
group by Z
having count(*) > 1) AS q ON q.z = t1.z

E 12°55'05.25"
N 56°04'39.16"

Maachie
Yak Posting Veteran

69 Posts

Posted - 2007-11-14 : 17:39:19

Thanks for your help and time but it is Quite confusing.
I have only one table called ace_users.
Is it possible that i can use select * and the aggregate field?

This below script returns nothing

Select * From ace_users as t1
inner join (
select chserialnum, count(*)as num from ace_users
group by chserialnum
having count(*) > 1 ) AS t2 ON t2.chserialnum = t1.chserialnum

Thanks,
Saru Brochu

Maachie
Yak Posting Veteran

69 Posts

Posted - 2007-11-14 : 18:03:32

This just returns only two columns just ignores Select *

Select * From
(
select chserialnum, count(*)as num from ace_users
group by chserialnum
having count(*) > 1
) AS Temp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources