How to identify a column in order by clause

From: todd_run <todd_run_at_yahoo.com>
Date: 31 Dec 2004 15:15:59 -0800
Message-ID: <1104534959.058255.150190_at_z14g2000cwz.googlegroups.com>

[Quoted] [Quoted] The question I have is: In a select statement that uses column aliases, should the order by clause include the column name or the column alias?

I'm wondering which of two syntaxes, if either, is considered more correct than the other. I'm working on an application that constructs SQL statements and I've come across a product that isn't working. The application constructs a SELECT statement with aliased column names, an aggregate and sorted results. Here's the basic statement structure:

select <table-name><column-name> as <column-alias>, [more columns,] sum(<table2-name><column-name>)
from
<table2-name> inner join <table-name>
on <join-condition> [more joined tables] group by <table-name><column-name> [,more columns] order by <table-name><column-name> [,more columns]

The product that is "breaking" requires the order by statement to use the <column-alias> instead of the <table-name><column-name>. Since the documentation does not indicate this is the expected behavior, I'm wondering if this is a bug. I'm also wondering if I were to change the application to use <column-alias> in the order by clause, would other products continue to work (it does work with at least one other database product that I have available for testing).

So again, the question is: In a select statement that uses column aliases, should the order by clause include the column name or the column alias? Any insight is appreciated.

Here is an example of what the tables and select statements might look like:

--Tables for experiment

create table customers(
customerNumber int not null,
firstName varchar(50) default null,
lastName varchar(50) default ' ' not null, primary key (customerNumber));

create table sales(
saleid int not null,
customerNumber int not null,
amount float,
primary key (saleid));

--Load some data into each table
--Not included

--Select statement using column alias to sort
select customers.lastName as Customer,
sum(sales.amount) from
sales inner join customers on
sales.customerNumber = customers.customerNumber group by customers.lastName
order by Customer;

--Select statement using column name to sort
select customers.lastName as Customer,
sum(sales.amount) from
sales inner join customers on
sales.customerNumber = customers.customerNumber group by customers.lastName
order by customers.lastName; Received on Sat Jan 01 2005 - 00:15:59 CET