CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
Saeid Alizade
Saeid Alizade

Reputation: 863

Select Query With Distinct on One Field

This is My Select Query:

SELECT 
[T1].[Id], [T2].[Id] , [T3].[Id] 
FROM  [T1]
INNER JOIN [T2] On [T1].[Id] = [T2].[T1Id]
INNER JOIN [T3] On [T2].[Id] = [T3].[T2Id]

The result is :

1  1  1
1  2  2
1  2  3
2  3  4
2  3  5
3  4  1

But what I need is distinct on [T1].[Id] Something like this:

1  1  1
2  3  4
3  4  1

This is not important the first record of Id returned , So the result could be:

1  2  2
2  3  4
3  4  1

but it is important that the columns of each record have a logical relations, So I can't Use Min or Max functions. The first solution I think is Insert values to a temp table and remove duplicates.

But I think that must be a better way? something in select query.

Does anyone have any idea about this?

Upvotes: 1

Views: 382

Answers (2)

praveen
praveen

Reputation: 12271

Try this :-

 Select 
 ID1,ID2,ID3
 from 
 (
   SELECT 
        [T1].[Id], [T2].[Id] , [T3].[Id] ,
        rn = ROW_NUMBER() OVER(partition by [T1].[ID] order by [T1].[ID])
   FROM  [T1]
   INNER JOIN [T2] On [T1].[Id] = [T2].[T1Id]
   INNER JOIN [T3] On [T2].[Id] = [T3].[T2Id]
)a
where a.rn = 1

Upvotes: 1

t-clausen.dk
t-clausen.dk

Reputation: 44316

;with a as
(
SELECT 
[T1].[Id] ID1, [T2].[Id] ID2, [T3].[Id] ID3, 
  row_number() over (partition by [T1].[Id] order by newid()) rn
FROM  [T1]
INNER JOIN [T2] On [T1].[Id] = [T2].[T1Id]
INNER JOIN [T3] On [T2].[Id] = [T3].[T2Id]
)
SELECT ID1, ID2, ID3
FROM a
WHERE rn = 1

Upvotes: 1

Related Questions