SQL: is it possible to ORDER BY just the column number?

[Dragonhunter]

Hello,

Is it possible to write an SQL statement that references a column number
in a database and not the name of the column?

Instead of this:
set rs = oConn.Execute("SELECT * FROM table ORDER BY ID)
I want to do this:
set rs = oConn.Execute("SELECT * FROM table ORDER BY column(0) )

Is it possible? Thanks!!

[Aaron Bertrand - MVP]

> Is it possible to write an SQL statement that references a column number

> in a database and not the name of the column?

No. You can use this proprietary syntax, but don't count on it always
working, and of course don't count on it working in other products.

-- order by col1:
SELECT col1, col2 FROM table ORDER BY 1

-- order by col2 DESC:
SELECT col1, col2 FROM table ORDER BY 2 DESC

Are you really going to be using SELECT * in production code???

[Mark Schupp]

ORDER BY <column_number>

is shown as ANSI standard SQL in my old SQL book ("Lan Times Guide to SQL")
so it should work with most SQL DBMSs.

--
Mark Schupp
--
Head of Development
Integrity eLearning
Online Learning Solutions Provider
[email]mschupp@ielearning.com[/email]
[url]http://www.ielearning.com[/url]
714.637.9480 x17


"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:%23h6exdvfDHA.1088@TK2MSFTNGP10.phx.gbl...

> > Is it possible to write an SQL statement that references a column number
> > in a database and not the name of the column?

>
> No. You can use this proprietary syntax, but don't count on it always
> working, and of course don't count on it working in other products.
>
> -- order by col1:
> SELECT col1, col2 FROM table ORDER BY 1
>
> -- order by col2 DESC:
> SELECT col1, col2 FROM table ORDER BY 2 DESC
>
> Are you really going to be using SELECT * in production code???
>
>

[Aaron Bertrand [MVP]]

> ORDER BY <column_number>

>
> is shown as ANSI standard SQL in my old SQL book ("Lan Times Guide to

SQL")

> so it should work with most SQL DBMSs.

That's column number in the SELECT list, not column number in the table. If
you use SELECT *, you shouldn't be relying on the "order" of the columns to
allow you to order by, IMHO.

[Jarl Hermansson]

"Mark Schupp" <mschupp@ielearning.com> wrote in message news:<eVyFm8vfDHA.2576@TK2MSFTNGP11.phx.gbl>...

> ORDER BY <column_number>
>
> is shown as ANSI standard SQL in my old SQL book ("Lan Times Guide to SQL")
> so it should work with most SQL DBMSs.

ORDER By <column_number> was valid in SQL-92, but not in SQL-99.

In SQL-99, the sort order is specified as:
<sort specification list>::=
<sort specification>[ {<comma><sort specification>}...]

<sort specification>::=
<sort key>[ <ordering specification>] [<null ordering>]

<sort key>::=<value expression>

<ordering specification>::=
ASC
| DESC

<null ordering>::=
NULLS FIRST
| NULLS LAST


As you can see above, a <value expression> has replaced SQL-92's
<column name> or <unsigned number>.

Note that writing "ORDER BY 3" is valid SQL-99 syntax, but doesn't
mean "order by the third column" as in SQL-92. My advice is: DO NOT
USE "order by column-number". Sooner or later the applications using
it just wont work like they used to. (No error code, just strange
behavior.)


Regards,
Jarl

[Mark Schupp]

Good point Aaron, I don't use select * so it didn't occur to me.

--
Mark Schupp
--
Head of Development
Integrity eLearning
Online Learning Solutions Provider
[email]mschupp2@ielearning.com[/email]
[url]http://www.ielearning.com[/url]
714.637.9480 x17


"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
news:eTs3bxzfDHA.2248@TK2MSFTNGP09.phx.gbl...

> > ORDER BY <column_number>
> >
> > is shown as ANSI standard SQL in my old SQL book ("Lan Times Guide to

> SQL")

> > so it should work with most SQL DBMSs.

>
> That's column number in the SELECT list, not column number in the table.

If

> you use SELECT *, you shouldn't be relying on the "order" of the columns

to

> allow you to order by, IMHO.
>
>

[Dragonhunter]

why is select * so evil?

-dragonhunter

Mark Schupp wrote:

> Good point Aaron, I don't use select * so it didn't occur to me.
>
> --
> Mark Schupp
> --
> Head of Development
> Integrity eLearning
> Online Learning Solutions Provider
> [email]mschupp2@ielearning.com[/email]
> [url]http://www.ielearning.com[/url]
> 714.637.9480 x17
>
> "Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
> news:eTs3bxzfDHA.2248@TK2MSFTNGP09.phx.gbl...

> > > ORDER BY <column_number>
> > >
> > > is shown as ANSI standard SQL in my old SQL book ("Lan Times Guide to

> > SQL")

> > > so it should work with most SQL DBMSs.

> >
> > That's column number in the SELECT list, not column number in the table.

> If

> > you use SELECT *, you shouldn't be relying on the "order" of the columns

> to

> > allow you to order by, IMHO.
> >
> >

[Ray at]

"Dragonhunter" <dragonhunter97@yahoo.com> wrote in message
news:3F70A0E2.142BFEC4@yahoo.com...

> why is select * so evil?

[url]http://www.aspfaq.com/2096[/url]

Ray at work

[Aaron Bertrand - MVP]

[url]http://www.aspfaq.com/2096[/url]

> why is select * so evil?

[Bob Barrows]

Dragonhunter wrote:

> why is select * so evil?
>
> -dragonhunter
>

1. It practically guarantees that you will pull data across the wire that
you don't really need, especially if your query involves joins.
2. It can cause problems in your code if new columns are added to your
tables.
3. You can't control the order in which the columns are retrieved.

Simply put, it's a bad, lazy habit. Selstar should be reserved for ad hoc
one-off queries, say in Query Analyzer, not production code.

Bob Barrows