Removing duplicates from query

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

Transact-SQL (2000)

Removing duplicates from query

Author

Topic

jmj
Starting Member

7 Posts

Posted - 2002-07-17 : 11:01:57

I have a query that pulls duplicate records from a table. I want to see all of the duplicates minus the students who have a flag set to yes on any of the duplicate records. How do I do this?
My query still shows the student's name/info minus the records that the flag was set to yes
So instead of getting this:
Tom
Tom
John
John
I will get
Tom
Tom
John
John
Paul
Here's my query. Thanks, J

SELECT s_LASTNAME, sFIRSTNAME,
s.StudentID, s.STATUS, s.Flag
FROM StudentDataDUMP s

WHERE (Flag Is NULL OR Flag='') AND (((s.StudentID) In (SELECT [s.StudentID] FROM [StudentDataDump s]
As Tmp GROUP BY [StudentID] HAVING Count(*)>1 )))
ORDER BY s.StudentID

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-07-17 : 11:29:38

This should do it...

select studentid, firstname, flag
from students
where studentid in (select studentid from students where flag = '' group by studentid having count(*)>1)

for testing...
create table students (
studentid int,
firstname varchar(20),
flag char(1)
)
insert into students
values(1, 'John', '')
insert into students
values(2, 'Paul', '')
insert into students
values(3, 'Tom', '')
insert into students
values(1, 'John', '')
insert into students
values(2, 'Paul', 'y')
insert into students
values(7, 'Graz', '')
insert into students
values(3, 'Tom', '')

hth,
Justin

jmj
Starting Member

7 Posts

Posted - 2002-07-17 : 11:46:56

Thanks- worked like a charm. I knew it was something easy- which reminds me how much I still have to learn.
j

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources