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

Question

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?

So if you have 1,1,2,3 and 2,3,2,3 and 1,1,2,2 and 1,6,2,3 what result do you want? I think you might be confused about what DISTINCT does - it applies to the whole set, not to each column individually. — Aaron Bertrand
– Aaron Bertrand, Commented Feb 25, 2012 at 21:48
Then please describe your data set and why you expect distinct to treat col3 and col4 differently than col1 and col2. Most importantly, show some sample data and what you expect the query to return. — Aaron Bertrand
– Aaron Bertrand, Commented Feb 25, 2012 at 21:57
Working on it, edits aren't coming across right for display. Figured this might be something in SQL Server or table setup -- apparently this is beyond comprehension. — user1233061
– user1233061, Commented Feb 25, 2012 at 22:00
The results don't make sense. Can you show a screen shot of this query with the results below? I suspect there is some alias going on or perhaps you have come across a bug or corruption. — Aaron Bertrand
– Aaron Bertrand, Commented Feb 25, 2012 at 22:11

Mithrandir · Accepted Answer · 2012-02-25 21:43:23Z

1

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

answered Feb 25, 2012 at 21:43

Mithrandir

25.5k6 gold badges53 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1233061 Over a year ago

All of the values of Column 3 end up as Column 4 values. None of Column 4's values show up as the data represented in Column 4: It's the same as column 3 (maybe I wasn't clear on this in my post). I fully expect that I'll only get the distinct values from both columns 3 and 4, as independent columns, not one replicated to the next. I've alised, pulled sub-queries, run into temp tables. I know this is something stupid-basic... Adding a "WHERE Col3 <> Col4" produces a blank result set.

Mithrandir Over a year ago

@user1233061: What do you mean by "None of Column 4's values show up as the data represented in Column 4"? Does the query result show values ic Col4 that are not in the original table?

user1233061 Over a year ago

Correct --> NOT the values in Col4 in the original table, the values from COL3.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related