how to display only one row of a duplicated id

Question

how to display only one row of a duplicated id

sc-w

Hall of Fame

Points: 3667
More actions
May 4, 2009 at 8:33 am

#133837

Hi,
I have a table that stores courses and the courses are duplicated in there so the course id's appears multiple times for each course.
I really need to select just one row where the course id exists and all the other columns, then insert it into another table. I have tried the DISTINCT function but because it works across the table my resutls are not what i need. I have spent hours looking for a solution but keep hitting a brick wall. Any help would be greatly appreciated.
The end result is to insert the unique records into another table with no duplicates, here is me query that i am stuck with but it's key to only taking one row per courseid
INSERT INTO wce_contact
(uniqueid, Course_Name, Qualification_Level, Qualification_Title, Awarding_Body)
SELECT DISTINCT
course_id, LONG_DESCRIPTION, NOTIONAL_NVQ_LEVEL_CODE, LEARNING_AIM_TITLE,
AWARDING_BODY_CODE
FROM wce_ilr AS ilr

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

How do you determine which result to transfer, if there are multiple records with the same ID, but different data in other columns? (If the data is the same in all columns, Select Distinct will handle it, so I'm assuming that other columns have variations in the data.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

Have you looked at row numbering? Selecting from below where Rowno = 1 to insert into your new table. For example:

;with numbered as(SELECT rowno=row_number() over

(Partition by course_id order by course_id), long_description, National_Nvq_lvl_code, learning_aim_title,awarding_body_code FROM yourtablesname) WHERE Rowno = 1

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

sc-w Hall of Fame Points: 3667 More actions · Answer 3

Thanks for yyour replies. Yes the data in the other columns for the duplicates are different but this doesn't matter i don't care which one i grab.

Someone on another forum mentioned using the max(field) which i think is working byut testing it at the moment. I will also look at your query bitbucket.

Thanks again