April 6, 2009 at 2:43 pm
Hi All,
I would like to know how to select only one record per id. the records are not duplicate. I am not able to apply distinct only on one particular column.
The data looks like this,
ID Column1 Column2
00002323733021427 0001
00002323733021470 0001
00002323734021427 0001
00002323734021470 0001
00003173730061886 0002
00003173730062885 0002
I want the result set like this
ID Column1 Column2
00002323733021427 0001
00002323734021470 0001
00003173730062885 0002
Thank You,
Ashu:-)
April 6, 2009 at 2:59 pm
What's the logic for which row remains? For the first set of dupes, you had 021427 for Column1, for the second you had 021470.
This is trivial to do in SQL 2008, but I do need to know which of the values in Column1 and Column2 you wish to keep for each ID.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 6, 2009 at 3:05 pm
Hi,
if you don't care which values from column1 and column2 you get you could use this query:
select id, max(column1) as maxcol1, max(column2) as maxcol2
from yourtable
group by id
Hope this helps 🙂
-----------------------
SQL Server Database Copy Tool at Codeplex
April 9, 2009 at 10:56 am
Thank u. It worked for max or min function.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply