selecting top 1 of each duplicate id with certin v

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

Transact-SQL (2005)

selecting top 1 of each duplicate id with certin v

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-06-14 : 09:44:23

i have a table like this, where id value appear more then once
with same or diffrent data on column val1 and val2 :

id val1(varchar 50) val2(varchar50)

333 axx fds
333 dfg null
333 ddd xxx
567 sd null
i want to get from this table, all uniuqe values, but with maximum data that there is on val1 and val2, for example :
id = 333 appears 3 time,and got data on both columns on row 1 & 3 ( on row 2 there is a null value on column2)
so i want to get a unique result like this :
333 axx fds
567 sd null

how do i do this?
Thanks
Peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-06-14 : 10:02:49

What determines the max data on column2 and column3?

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-06-14 : 10:13:24

quote:
Originally posted by sodeep

What determines the max data on column2 and column3?

max data in this case that in on of the rows where
val1 and val2 are both with data (not null)
if not then at least on of the coulmn will have data (beteen val1 and val2)

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-06-14 : 11:08:35

[code]Select Id,Val1,Val2
from(
Select *,ROW_NUMBER() OVER (PARTITION BY Id Order by
Case When Coalesce(Val1,Val2) is not null then 1
When Coalesce(Val1,Val2) is null then 3
Else 2 End)as ROWID from Table)Z
Where Z.ROWID = 1[/code]

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-06-14 : 11:56:39

thanks for the solution
a small improvment must be done in the order by to match what i needed :


Select Id,Val1,Val2 
from(
Select *,ROW_NUMBER() OVER (PARTITION BY Id Order by 
 (case when val1 is not null then -1 else 0 end)+(case when val2 is not null then -1 else 0 end) )
as ROWID from Table)Z
Where Z.ROWID = 1

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-06-14 : 12:05:43

I haven't tested but are you sure you solutions will suffice with all possibilities.

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-06-14 : 16:21:05

yes
i have checked this
you solution gives the exect opposite result from the one that i need
but you gave me most of the way to the solution
Thanks Alot!

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-06-16 : 09:51:02

by the way
how do i do the same query in sql 2000 where i dont have the ROW_NUMBER option?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-18 : 02:36:59

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources