CODEWITHSUNDEEP
selectsql-server-2008-r2
user1233061
user1233061

Reputation: 11

SELECT different columns, SELECT DISTINCT returns same value in both columns

SELECT Col1, Col2, Col3, Col4
FROM DataSource
WHERE Criteria = FK

-- All columns okay.

SELECT DISTINCT Col1, Col2, Col3, Col4
FROM DataSource
WHERE Criteria = FK

-- Columns 3 & 4 are the same!!! -- The data IS DIFFERNET

What would cause this?

It's the same query with DISTINCT, but the values from Col3 are the same in Col4.

I've checked a variety of things, backed-up and exported the data, re-created the table. This is a table-direct query (not a view or anything else). There are no triggers.

I can't place it...

Any thoughts / ideas welcome.

Select: 

OGID    MID PN      OPN
35      78  610131  204180001A
35      78  610132  204215001A
35      78  610133  204183001A
35      78  610134  204273001A
35      78  610135  204275001A
35      78  610137  204262001A
35      78  610152  204264001A
35      78  610203  204332001A
35      78  610266  204243001A
35      78  610285  204080001A
35      78  610286  204219001A
35      42  610289  130211

Select Distinct: 

OGID    MID PN      OPN
35      78  610131  610131
35      78  610132  610132
35      78  610133  610133
35      78  610134  610134
35      78  610135  610135
35      78  610137  610137
35      78  610152  610152
35      78  610203  610203
35      78  610266  610266
35      78  610285  610285
35      78  610286  610286
35      42  610289  610289

Why?

Upvotes: 0

Views: 1300

Answers (1)

Mithrandir
Mithrandir

Reputation: 25377

What do you expect? Your query 

SELECT DISTINCT Col1, Col2, Col3, Col4
FROM DataSource
WHERE Criteria = FK

selects distinct combinations of values in all four selected columns. It's possible to have repeated values in some of the columns. The following "result" would be considered to be distinct:

Col1 Col2 Col3 Col4
A    A    A    1
A    A    A    2

Upvotes: 1

Related Questions