Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
figmo
Starting Member
18 Posts |
 Posted - 2011-04-27 : 19:06:07
|
| What if the group by column is not a column at all but a calculated column. Like this...SELECT CASE WHEN Artist IS NULL THEN Assistant ELSE Artist END AS ArtistOrAssistant, MAX(DateAdded) FROM Library GROUP BY ArtistOrAssitantMeaning "give me the distinct names all artists (or assistants if the artist is unknown) and the latest date something was added for them"I tried the above syntax and got a "invalid column name 'ArtistOrAssistant'" error. Am I screwed? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-27 : 20:56:57
|
You can also doSELECT CASE WHEN Artist IS NULL THEN Assistant ELSE Artist END AS ArtistOrAssistant, MAX(DateAdded) FROM Library GROUP BY ArtistOrAssitant CASE WHEN Artist IS NULL THEN Assistant ELSE Artist END This behavior is a funny side effect of the logical sequence in which the query is processed. The select clause is one of the very last phases (just before order by clause if there is one). So the group by clause which is done before the select clause does not know about the column alias you created in the select clause. |
 |
|
|
|
|
|
GROUP BY and 'psuedo' columns