get all unique records - 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

SQL Server 2000 Forums

Transact-SQL (2000)

get all unique records

Author

Topic

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-04-07 : 03:32:29

hello,
i have a table with duplicate records for courses in the column course_name.

am trying to sort out the uniqe records, here are my queries

1. 
select courseid, course_name  from training_courses group by courseid, course_name having count(course_name) >= 1

and 
2. select  distinct course_name, courseid  from training_courses

however the courseid is a unique auto increment key, so whenever i add it to the query it brings out all the records.

I only want unique course_name records

thanks
afrika

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-04-07 : 03:59:53

afrika - Not sure if you want 1. all the course_names that currently only have one instance, or 2. to return only the distinct course names with an id

1. 
SELECT   MIN(tc.courseid) AS courseid, 
         tc.course_name 
FROM     dbo.training_courses 
GROUP BY tc.course_name 
HAVING   COUNT(courseid) = 1

2.
Clearly, if you're returning multiple courseids for a single course_name and you want to limit it to one, you're going to have to apply an aggregate function to courseid - it's up to you which, i.e.

SELECT   MIN(tc.courseid) AS courseID, 
         tc.course_name 
FROM     dbo.training_courses 
GROUP BY tc.course_name

Which ends up exactly the same as case 1 minus the HAVING clause.

Mark

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-07 : 04:13:12

>>I only want unique course_name records

with id column?

Madhivanan

Failing to plan is Planning to fail

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-04-07 : 05:06:11

hiya Mark,
thanks a million, number 2 works like a charm, guess i have to go back to the books

hi Maddy, yes with column id, but only those with distinct courses.

thanks

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-04-07 : 10:05:40

no probs afrika

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources