July 15, 2008 at 4:52 am
Hi
I am sorry if someone has already posted this question and am asking again.
This problem is eating my head, I have a table
Table 1 -
1 br
1 er
2 rt
3 yh
2 uj
3 iu
3 ol
Now I want only 1 row for each unique id, that row could be any row but id shouldn't repeat.
1 br
2 rt
3 yh
or
1 er
2 uj
3 ol
Thanks
July 15, 2008 at 5:03 am
Here are a few ways...
select a, min(b) as b from [Table 1] group by a
select a, max(b) as b from [Table 1] group by a
; with x as (select *, row_number() over (partition by a order by a) as row from [Table 1])
select a, b from x where row = 1
Use this to physically delete 'duplicate' data from your table...
; with x as (select *, row_number() over (partition by a order by a) as row from [Table 1])
delete from x where row > 1
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
July 15, 2008 at 5:07 am
DECLARE @T Table (ID int , name varchar(50))
INSERT INTO @T
Select 1,'br' UNION
Select 1,'er' UNION
Select 2,'rt' UNION
Select 3,'yh' UNION
Select 2,'uj' UNION
Select 3,'iu' UNION
Select 3,'ol'
Select ID,Name From
(SELECT ID,Name,Row_Number() OVER ( Partition By ID Order By Name) Rank FROM @T) As Z
Where Rank = 1
July 15, 2008 at 5:43 am
Thanks for the replies!
It worked.
Is there any way to do this without the OVER clause?
Also can you also suggest me a good article on the window aggregate functions. The OVER clause is not completely known to me.
Thanks!
July 15, 2008 at 5:53 am
Hi Sanchita,
BOL is best to learn about that.
Just type the keyword in INDEX of BOL and see the explaination.
July 15, 2008 at 6:00 am
Thanks!
Well we have found another way to do the same without OVER
select * from testtable
select distinct id, (select top 1 datavalue from testtable where id = t1.id) from testtable t1
July 15, 2008 at 6:08 am
Even you can use the query suggested by Ryan
select a, min(b) as b from [Table 1] group by a
select a, max(b) as b from [Table 1] group by a
July 15, 2008 at 6:37 am
Yes absolutely.
Thanks to both of you!
July 15, 2008 at 6:40 am
Nice logic hari..
Thanks for posting the code.....
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply