CODEWITHSUNDEEP
sqlsql-server
Rupert
Rupert

Reputation: 1294

Select Distinct Sql Server Question

I have the following table structure.

 A | B | C

I wish to pull column A and B where all the results of A and B are distinct. I wish to ignor column C. My unerstading of the distinct keyword that it looks at the whole row not jst the columns you return. Any ideas how I could do this?

Upvotes: 0

Views: 317

Answers (4)

sonudx
sonudx

Reputation: 1

select distinct a,b,row_number()over (partition by a order by a) as rn into #T1 from (select distinct b,a from table)d

update #T1 set a =null where rn > 1

select a,b from #T1

Upvotes: 0

user200927
user200927

Reputation:

SELECT DISTINCT doesn't look at the whole row in the database, it just looks at the requested columns. So you can just do SELECT DISTINCT a, b FROM mytable and it will completely ignore column c.

Upvotes: 0

user114600
user114600

Reputation:

Not so. DISTINCT looks at whatever columns you specify. So for you, SELECT DISTINCT A, B FROM table.

I'd prefer the GROUP BY though: SELECT A, B FROM table GROUP BY A, B

Upvotes: 3

lutz
lutz

Reputation:

Your understanding is wrong. DISTINCT does not look at the whole row. Did you try something like this?

SELECT DISTINCT(A, B) FROM t WHERE ...

Upvotes: 2

Related Questions