Column Headings in Result Sets

Does the ANSI SQL Standard specify how columns are named in result sets?

I ask this because I just came across some behavior that surprised me, or went against my expectations.

Given the table: t1 (c1 int)
and this query: select t1.c1 from t1

the results are displayed as follows on all databases I tried it on (SQL Server, Oracle, DB2, mySQL and Sybase):

c1
--
1
2
..etc

I would have expected the column heading to be different, as follows:

t1.c1
--
1
2
...etc

So, if you say 't1.c1' in the select list, you should see t1.c1 in the column heading in the result set.

Given another table, t2 (c1 int), the column headings in the result set of the query 'select t1.c1, t2.c1 from t1,t2' were the same on all database platforms:
c1 | c1
----
1 | 2
2 | 3

This strikes me as ambiguous, because how do you know which result column came from which table ? I know that you can use column aliases if you want unique names in your column headings, but is there a good reason why the column headings 't1.c1' and 't2.c1' wouldn't be used by default if you specify 't1.c1' and 't2.c1' as items in your select list?

Thanks,
Colm.... is there a good reason why the column headings 't1.c1' and 't2.c1' wouldn't be used by default if you specify 't1.c1' and 't2.c1' as items in your select list?yes -- because by the time the result set is constructed, the database has forgotten which table each column came from

:)

more accurately, a column name is an identifier, whereas "t1.c1" is a string

you could always do this, if you really need it --

select t1.c1 as "t1.c1", ...

Column Alias Behavior

I don't know why I never noticed this before, but in the code below, why can
I ORDER BY an aliased column, but I have to use the *actual* column name or
expression in the GROUP BY?
I would really rather say "GROUP BY Column1, Column2"
CREATE TABLE msl_T1 (
col1 INT,
col2 INT,
col3 INT
)
GO
SELECT col1 AS Column1, col2 * 2 AS Column2, MIN(col3) AS Column3
FROM msl_T1
GROUP BY col1, col2 * 2 -- RIGHT HERE!!
ORDER BY Column3
DROP TABLE msl_T1
GO
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave MustaneYou can use column aliases in the ORDER BY, but nowhere else. You can use a
derived table to circumvent this:
SELECT Column1, Column2, min (col3) Column3
FROM
(
SELECT col1 AS Column1, col2 * 2 AS Column2, col3
FROM msl_T1
) X
GROUP BY Column1, Column2
ORDER BY Column3
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eMlNTrE9FHA.4084@.TK2MSFTNGP10.phx.gbl...
>I don't know why I never noticed this before, but in the code below, why
>can I ORDER BY an aliased column, but I have to use the *actual* column
>name or expression in the GROUP BY?
> I would really rather say "GROUP BY Column1, Column2"
> CREATE TABLE msl_T1 (
> col1 INT,
> col2 INT,
> col3 INT
> )
> GO
> SELECT col1 AS Column1, col2 * 2 AS Column2, MIN(col3) AS Column3
> FROM msl_T1
> GROUP BY col1, col2 * 2 -- RIGHT HERE!!
> ORDER BY Column3
> DROP TABLE msl_T1
> GO
>
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||> You can use column aliases in the ORDER BY, but nowhere else.
HMPH!! So much for "consistency". As Bill the Cat so eloquently put it,
"PTHPTHPTPTH" :-)
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||It's not a question of consistency.
5aa6fc669a8" target="_blank">http://groups.google.ca/group/comp.../>
5aa6fc669a8
and read Joe Celko's explanation on Select.
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eWwqf5E9FHA.2676@.TK2MSFTNGP15.phx.gbl...
> HMPH!! So much for "consistency". As Bill the Cat so eloquently put it,
> "PTHPTHPTPTH" :-)
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>