Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.media.kyoto-u.ac.jp!postnews.google.com!z14g2000cwz.googlegroups.com!not-for-mail
From: "todd_run" <todd_run@yahoo.com>
Newsgroups: comp.databases.theory
Subject: How to identify a column in order by clause
Date: 31 Dec 2004 15:15:59 -0800
Organization: http://groups.google.com
Lines: 68
Message-ID: <1104534959.058255.150190@z14g2000cwz.googlegroups.com>
NNTP-Posting-Host: 209.102.126.97
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1104534963 4202 127.0.0.1 (31 Dec 2004 23:16:03 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 31 Dec 2004 23:16:03 +0000 (UTC)
User-Agent: G2/0.2
Complaints-To: groups-abuse@google.com
Injection-Info: z14g2000cwz.googlegroups.com; posting-host=209.102.126.97;
   posting-account=he_SEwwAAACjjob5nMtiO6RfG0c53B1K
Xref: dp-news.maxwell.syr.edu comp.databases.theory:29244

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;