select with first occurance of field if duplicate

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 2008 Forums

Transact-SQL (2008)

select with first occurance of field if duplicate

Author

Topic

mruprai
Starting Member

12 Posts

Posted - 2012-10-30 : 09:07:37

i have a table with duplicate ID(due to some bad staff data).

I want to select from this table, where if the ID is a duplicate, just display the first occurance.

Any ideas?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-10-30 : 09:10:47

How do you decide which ROW is the first?
Are there other columns involved and are the values different or equal?

An example would be fine

Too old to Rock'n'Roll too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-30 : 09:46:53

see point (6) here

http://beyondrelational.com/modules/2/blogs/70/posts/10802/multipurpose-rownumber-function.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-30 : 09:51:31

with cte as
(select *, seq = row_number() over (partition by id order by mycol))
select * from cte where seq = 1

You could also you a group by on id and an aggregate on all the other columns.

Better would be to correct the underlying data.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mruprai
Starting Member

12 Posts

Posted - 2012-10-30 : 11:50:56

Example:
Staff_List (Table)
(Staff_Number, Name)
(A, 'John')
(A 'John S')

Order (table)
(orderNumber, Staff_number)
(1,A)

when i join i will get 2 orders, but i want there to be 2 records(the same order twice).

So how do i do a select on Staff_list, so only one(dont mind which) record shows.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-30 : 12:18:33

[code]
SELECT o.OrderNumber,s.Name
FROM Order o
INNER JOIN(SELECT ROW_NUMBER() OVER (PARTITION BY Staff_Number ORDER BY Staff_Number) AS Seq,*
FROM Staff_List)s
ON s.Staff_Number = o.Staff_number
AND s.Seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources