Help please with duplicate records

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)

Help please with duplicate records

Author

Topic

hantastic
Starting Member

3 Posts

Posted - 2008-11-24 : 14:17:29

dpatter
Starting Member

2 Posts

Posted - 2008-11-24 : 14:45:52

Hi, I found this on the internet, just modify for your use.

;With CTE as (Select *, Row_Number() Over(Partition By item order by item) Rowid From #temp)
Delete from CTE Where RowId>1

forwheeler
Starting Member

44 Posts

Posted - 2008-11-24 : 14:49:51

CTE is available in SQL2005, 2008.

hantastic
Starting Member

3 Posts

Posted - 2008-11-24 : 15:01:55

Thank you for that. Unfortunately I haven't a clue how to modify that for my use. I am a relatively new SQL user. Could you please assist?

dpatter
Starting Member

2 Posts

Posted - 2008-11-24 : 15:15:16

Sorry, I should have read the title a little closer, CTE as forwheeler pointed out is only available in SQL2005 and 2008

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-24 : 15:18:20

What you want to do with this?

quote:
Originally posted by hantastic

Hi,

I wonder if any clever SQL developers would please mind helping me out with a query.
I have a query that is returning duplicates in one of the columns. I have tried using distinct etc but I am still getting duplicates. Here is the SQL

SELECT media.thumb, media.mediaTitle, lightbox.lightboxName, lightbox.id, lightbox.userID
FROM media
INNER JOIN lnkLightbox ON media.id = lnkLightbox.mediaID
INNER JOIN lightbox ON lnkLightbox.boxID = lightbox.id
WHERE (lightbox.userID = 2)
ORDER BY lightbox.id DESC

I am getting the following resulting data...

thumb | mediaTitle | lightboxName | id | userID
__________________________________________________________
aeron_2_thumb.jpg | Test | Test | 5 | 2
aeron_cutout_thumb.jpg | Chair | Test | 5 | 2aeron_3_thumb.jpg | test2 | Test2 | 4 | 2
aeron_2_thumb.jpg | Test | Test2 | 4 | 2
aeron_cutout_thumb.jpg | Another Chair | Test2 | 4 | 2What I want to do is only show one example image per 'lightboxName'
so I am hoping to achieve the following results...

thumb | mediaTitle | lightboxName | id | userID
__________________________________________________________
aeron_2_thumb.jpg | Test | Test | 5 | 2
aeron_3_thumb.jpg | test2 | Test2 | 4 | 2

Basically I want there to be no duplicates unde the column 'lightboxName'. Is that possible? If so what would I have to change in my SQL?

thanks in advance!!

hantastic
Starting Member

3 Posts

Posted - 2008-11-24 : 15:20:28

I just want to not show the rows that have duplicates in the lightBox name field. Can you just specify DISTINCT for one column?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources