Inconsistent or reasonable? - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Inconsistent or reasonable?

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-05 : 16:44:17

SELECT expression1 AS MyCol

FROM MyTable

GROUP BY expression1 -- SQL 2000 won't accept MyCol here

ORDER BY MyCol -- But it works fine in ORDER BY

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-05 : 20:10:25

Not inconsistent nor unreasonable. ORDER BY is the last operation to occur, and it can be assumed that it's working on a set of results as if they were in a regular table. I wouldn't be surprised if SQL Server (and other products) actually put the query results into a work table and order it from there.

GROUP BY has to evaluate the expression to work correctly anyway, so even if it could use the alias it's not saving anything except typing. There are also cases where you can write an expression in the SELECT clause while GROUPing BY another expression.

IIRC the original ANSI spec did not allow a column alias in ORDER BY, it's possible that's still true (haven't read the spec lately). And I know that some people were calling for the feature to be removed, meaning that all ORDER BY expressions have to be referenced by the expression and not its alias.

The only function that column aliases have is to give a heading name for query results...nothing more.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-05 : 20:47:50

I think also UNION's factor into this ....

why?

because:

select a.col1 as Column
from table1
union all
select b.col1
from table2
order by ??????

you can't say ORDER BY a.COL1 -- that makes no sense. You also can't say ORDER BY b.COL1 -- that also makes no sense. What does make sense is to order by the combination of col1 from table1 and table2. But how do you specify that? by using the Alias.

(this is just a guess ... i have absolutely no idea

)

- Jeff

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-05 : 20:52:37

Good point, I didn't think of that.

I think by the time you throw in TOP, HAVING, and god knows what else, it's probably near impossible to have the parser even recognize all of the possibilities available.

shsmonteiro
Constraint Violating Yak Guru

290 Posts

Posted - 2003-08-05 : 23:35:43

What a philosofical reflexion!!!

Well, just to clarify Jeff question:

When UNIONing its legal to ORDER BY any column or alias referenced in the first (top most) SELECT. So, you can use:



select a.c1 as col
from teste a
union all
select b.c1 
from teste1 b
order by col

select a.c1 as col
from teste a
union all
select b.c1 
from teste1 b
order by a.c1

select c1 as col
from teste
union all
select c1 
from teste1
order by teste.c1

but cannot use the following:



select c1 as col
from teste
union all
select c1 
from teste1
order by teste1.c1

select a.c1 as col
from teste a
union all
select b.c1 
from teste1 b
order by teste.c1

SQL Server Way of Life

Sérgio Monteiro
Trust in no Oracle

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-06 : 11:15:36

Thanks everyone.

The UNION / ORDER BY business was an unexpected bonus of this thread.

I don't have a personal issue with coding the expression in GROUP BY , other than it can be lengthy if the expression is lengthy. CASE statements and aggregate functions tend to creap into many of my GROUPED SQL statements. An Alias would occasionally be easier to read / easier to understand in a GROUP BY list.

I can't find any plausable reason why GROUP BY could not support an alias, but I appreciate the feedback. I thought I may have overlooked something.

Sam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-06 : 11:40:32

Remember, you don't always want to GROUP BY exactly what you are returning. This is a very common SQL mistake.

For example, take a look at:

Select FirstName + ' ' + LastName as FullName
FROM
SomeTable
GROUP BY FirstName + ' ' + LastName

How does that look to you? you might even say "Boy, I wish I could just say GROUP BY FullName".

BUT --

In my humble (and admittely "SQL-snobby") opinion, the query SHOULD be written as:

Select FirstName + ' ' + LastName as FullName
FROM
SomeTable
GROUP BY FirstName, LastName

Because that should be more efficient (depending on indexes) and is truly what you wish to do -- return all combinations of firstname, lastname and then concatenate them together.

So, don't group by the entire expression you are returing to avoid error messages -- sometimes choosing your grouping's carefully can really improve a queries performance AND readability.

Another great example is if you have:

select 'The date is ' + convert(varchar(20), someDate) as NewDate
from table
group by 'The date is ' + convert(varchar(20), someDate)

that should be written as:

select 'The date is ' + convert(varchar(20), someDate) as NewDate
from table
group by someDate

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-06 : 11:55:49

Hi Jeff,

Yep, I agree with you and Rob. There are plenty of good reasons why both GROUP BY and ORDER BY should support items that are NOT in the SELECT list.

Sam

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2003-08-06 : 13:53:01

robvolk:

The possibility to use a correlation name in the order by clause was introduced in SQL 99. At the same time support for ordering by expressions and columns not in the select list was introduced. The semantics that

order by 3

should mean order by the 3:rd expression in the select list was made obsolete.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-06 : 19:24:53

Thanks Lars.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources