Let's take a look at another one of those stupid, arbitrary SQL Server error messages
that Bill Gates clearly only created because Micro$oft is evil and incompetent and they want to annoy us (and probably kill baby squirrels, too):
Msg 145, Level 15, State 1, Line 4
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
This message pops up when you ask for DISTINCT rows for one set of columns, but you'd like to have the results ordered by one or more columns not
specified in your distinct set. For some reason, SQL Server will not allow this! Why not?
Let's look at an example. Suppose we have the following data:
From that, let's say we'd like to see only DISTINCT
letters, ordered by value. Clearly, this means we want to return:
... right? "A" has the lowest values of 1, followed "C" with a value of 2, and finally "B" with values of 3. Yet, if we write:
select distinct letter
order by Value
we receive back the above error message. Why?
Remember, the "rules" of what is allowed and not allowed in SQL is not determined by how your data looks at a particular point in time; it is a constant. Just because one set of data seems
to be perfectly orderable in this scenario does not
mean that all
data is always going to be perfectly orderable as well. What if we had this set of data instead:
Look carefully at the above, and think about what should be returned when asking for distinct Letters ordered by Value.
The answer is .... there is no correct answer! B contains a value of both 0 and 3, so should it appear first
, or should it appear last
? The answer is -- who knows?! It is not specified when simply asking for distinct Letters ordered by Value. It is not a clear, complete, deterministic question that you are asking SQL Server to answer; it is like asking someone "what color is it when you add 4 and 3?" -- the question makes no sense and cannot be conclusively answered as stated.
Now, one person might say, "well, clearly, we want to B to be returned first
, since it has the value of 0 and that is lower than all the other values", and another might say "well, clearly, we want B to be returned last
, since it has the value of 3 and that is clearly higher than all the other values." And both people would technically be right, and both are entitled to their logic and their needs -- but that logic and those requirements need to be specified; they cannot be implied, since there is more than one way to interpret the question. Computers are funny that way, right? They are pretty darn smart, but they always insist on being told exactly
what it is we want. How annoying!
(I often use this analogy regarding users or programmers who refuse to express their requirements or needs logically and completely: It is like a magic Genie saying "you can have anything in the world, all you need to do is tell me specifically what you want
and make it perfectly clear
and you will get it!"
, and the users thinking about it, and eventually concluding "nahh... not worth it, sounds like too much work; can't you just figure out what I want?")
So, what is the solution here? To make the error "just go away", we can try just adding the Value column to the SELECT clause like this:
select distinct Letter, Value
order by Value
In our original set of data, this works fine -- it returns the results we want and expect, only with an additional column. If your data has a 1-1 relation between the ORDER BY columns and the SELECT columns, then simply adding the ORDER BY columns to your SELECT does the trick.
However, running that SELECT on the second set of data (in which "B" has values of 0 and 3) results in "B" being repeated twice:
To solve this, like usual, we simply must decide what we want, and explicitly tell SQL Server what that is. If we want to order by the letter using the lowest associated value, then we simply ask for that ... not by using DISTINCT, but by using GROUP BY:
group by Letter
order by min(Value)
This results in:
... And if we want to do the opposite, and order by the Letter with the greatest associated value, then we write:
group by Letter
order by max(Value)
We may even want to add up all of the values for each letter and order by that, or maybe there is some other rule. The key is that SQL Server cannot help you until you specifically express what you are looking for. And that is what this error message is all about -- "Hey, what you are asking for can be interpreted many different ways; can you be more specific?"
SQL (and any other programming language) really is like the magic Genie; it can give you whatever you want, but there is a catch -- you need to ask for it!