Comparing rows within the same table (duplicates)? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Comparing rows within the same table (duplicates)?

Author

Topic

AJL
Starting Member

7 Posts

Posted - 2007-06-15 : 19:25:33

How do I only select rows with duplicate dates for each person (id)? (The actual table has approximately 13000 rows with approximately 3000 unique ids)

p_key id date pulse
--------------------------------------
1 32 5/25/2006 80
2 32 5/25/2006 85
3 32 4/26/2006 81
4 32 6/15/2006 82
5 15 1/20/2006 75
6 15 3/25/2006 80
7 15 3/25/2006 83

Result table I am looking for:
p_key id date pulse
------------------------------------
1 32 5/25/2006 80
2 32 5/25/2006 85
6 15 3/25/2006 80
7 15 3/25/2006 83

Thanks.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-15 : 20:08:22

[code]
select t1.*
from MyTable t1
join (
select id, date
from MyTable
group by id, date
having count(*) > 1
) t2 on t1.id = t2.id and t1.date = t2.date
[/code]

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-16 : 00:45:00

I think exists would be faster in this case


select  t1.*
from    MyTable t1 
where exists
         (
              select  id, date 
              from    MyTable
              where id = t1.id and date = t1.date 
              group by id, date
              having  count(*) > 1
             )

Madhivanan

Failing to plan is Planning to fail

AJL
Starting Member

7 Posts

Posted - 2007-06-16 : 01:52:52

Thank you both for your help.
This is probably a dumb question, but since I am new to SQL, could you please explain to me the difference between t1.* and t1*?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-16 : 02:05:12

quote:
Originally posted by AJL

Thank you both for your help.
This is probably a dumb question, but since I am new to SQL, could you please explain to me the difference between t1.* and t1*?

t1.* is valid syntax and t1* is not

Madhivanan

Failing to plan is Planning to fail

AJL
Starting Member

7 Posts

Posted - 2007-06-16 : 02:35:44

Sorry, I think I meant to ask if Select t1.* is the same as Select* ? What is Select t1.* actually selecting?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-16 : 02:39:38

select * will returns all the column. In this case, you have a table join so it will return all columns both table t1 and t2 (a derived table)

select t1.* only returns all columns from table t1

KH

AJL
Starting Member

7 Posts

Posted - 2007-06-16 : 02:48:07

Got it. Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-16 : 03:01:18

Also it is better practice to name all columns instead of t1.* (t1.col1,t1.col2,...)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources