June 14, 2009 at 7:44 am
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
June 14, 2009 at 7:58 am
Try this
; WITH cte AS
(
SELECT
id,
val1,
val2,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY val1, val2) rownum
FROM @t
)
SELECT
*
FROM cte
WHERE rownum = 1
Flo
June 14, 2009 at 8:15 am
ok the code work,but when i try to add an order by i get this error :
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
this is the code:
WITH RemoveDuplicate AS
(
SELECT
(case when val1 is not null then 1 else 0 end)+(case when val2 is not null then 1 else 0 end),
id,
val1,
val2,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY val1, val2) as rownum
FROM RemoveDuplicateId
order by (case when val1 is not null then 1 else 0 end)+(case when val2 is not null then 1 else 0 end) desc
)
SELECT
*
FROM RemoveDuplicate
WHERE rownum = 1
June 14, 2009 at 9:52 am
Remove this line:
order by (case when val1 is not null then 1 else 0 end)+(case when val2 is not null then 1 else 0 end) desc
Flo
June 14, 2009 at 9:59 am
i have move to row into :
ROW_NUMBER() OVER (PARTITION BY id ORDER BY case when val1 is not null then 0 else 1 end)+(case when val2 is not null then 0 else 1 end) ) as rownum
which solved the problem, and gave me the correct result i needed
June 16, 2009 at 8:04 am
how do i do the same in sql 2000 where the row number ability dosent exist?
June 17, 2009 at 2:11 am
In SQL Server 2000 your table needs any other unique column (e.g. a identity, guid or timestamp). If it doesn't have one of those a very slow WHILE loop seems to be the only way, AFAIK...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply