ORDER BY clause with unknown column name

can i use an unknown column in an ORDER BY clause with t-sql?
i know it will always be an identity field and it is in the first column.
it is also the primary key.
can i depend on a recordset always being in this order without the the
clause?> can i use an unknown column in an ORDER BY clause with t-sql?
> i know it will always be an identity field and it is in the first column.
> it is also the primary key.
> can i depend on a recordset always being in this order without the the
> clause?
Without the what clause?
Are you using SELECT *? Why? This is a preferably avoidable technique in
production code.
You can try using the constant 1, e.g.
SELECT column1, column2, column3
FROM dbo.Table
ORDER BY 1;
This will order by the first column, usually, but it can cause you problems
later, e.g. compare these:
SELECT
[2] = 'b',
[1] = 'a'
UNION
SELECT
[2] = 'a',
[1] = 'b'
ORDER BY 1;
SELECT
[2] = 'b',
[1] = 'a'
UNION
SELECT
[2] = 'a',
[1] = 'b'
ORDER BY [1];
(There are also some other funny rules and bugs I've seen by using constants
in ORDER BY, I can dig them up if need be... I think Steve Kass has posted a
few here.)
Also, if you are using SELECT * (did I mention this was terrible programming
practice and opens a can of barracudas?), can you really rely on your
co-workers to never change the column structure (either intentionally or
accidentally)?
It is trivial to generate a column list, either up front or on the fly, for
any table you are selecting from (especially if you only have to do it once,
e.g. when you create the view or procedure). So I'm not sure I believe that
you will be gaining anything by using * and not having to know the first
column name, because there are a lot of downsides.|||>> can i use an unknown column in an ORDER BY clause with t-sql?
There is no such thing as an unknown column in t-SQL. Use either a column
name or an alias or expression ( with certain limitations ) in the ORDER BY
clause to sort the data the way you want.
Disregarding the visual representation, to sort by the default identity
column in the table you can use:
ORDER BY $IDENTITY
Note that is is only applicable in SQL 2005.
No, you should not depend on any kind of ordering unless you explicitly
included the ORDER BY clause.
Anith|||In addition to what Aaron said, you have no guarantee that the column "n",
where n is the ordinal position of a column instead of a name, is the column
you actually want to order by. If the table were changed, columns added or
removed, or indexes altered, you could end up with a dog of a query trying
to order by column number.
"mcnewsxp" <mcourter@.mindspring.com> wrote in message
news:OvTSanVkGHA.4660@.TK2MSFTNGP05.phx.gbl...
> can i use an unknown column in an ORDER BY clause with t-sql?
> i know it will always be an identity field and it is in the first column.
> it is also the primary key.
> can i depend on a recordset always being in this order without the the
> clause?
>|||order by 1 should do the trick then.
thanks for the warings.
BTW - the column name is known it is just different in different tables.
i inherited what i have and don't want to cahnge too many things because i
have to submit scripts to the DBAs that have to be applied to a couple of
different DBs. just lazy i guess.
thanks much.|||>> can i use an unknown column in an ORDER BY clause with t-sql? <<
No, you have to sort by something. When do not put the ORDER BY in a
cursor (it is not part of a SELECT, another common newbie assumption),
then the engine can out the rows into a sequence in any order. Every
SQL product will be a bit different, depending on physical storage,
parallelism in the hardware, etc.
You might want to read a book and find out why IDENTITY can *never* be
a key. By definition. What you are doing is mimicing a 1950's magnetic
tape file in SQL. The IDENTITY is an exposed physical locator you are
using, the same way we used record positions on a mag tape.
No. This is the definition of a table -- it is a set without any
physical ordering. When you finally read a book on RDBMS, pay
attention to "The Information Prinicple" and some of the other rules
that Dr. Codd set up.
There are some proprietary kludges you can use to destroy portability
and data integrity. For example, there is a ordinal position number
that was removed from Standard SQL a few years ago, but exists in some
products.
All you will get in Newsgroups are the kludges; you need to get an
education. And it will take you at least a year to do that. Your
whole mindset is wrong and you have to unlearn a lot.|||I think the ORDER BY using an ordinal is getting deprecated in a future
version, I'm sure I've read it somewhere...
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uXuBpsVkGHA.3536@.TK2MSFTNGP05.phx.gbl...
> Without the what clause?
> Are you using SELECT *? Why? This is a preferably avoidable technique in
> production code.
> You can try using the constant 1, e.g.
> SELECT column1, column2, column3
> FROM dbo.Table
> ORDER BY 1;
> This will order by the first column, usually, but it can cause you
> problems later, e.g. compare these:
> SELECT
> [2] = 'b',
> [1] = 'a'
> UNION
> SELECT
> [2] = 'a',
> [1] = 'b'
> ORDER BY 1;
> SELECT
> [2] = 'b',
> [1] = 'a'
> UNION
> SELECT
> [2] = 'a',
> [1] = 'b'
> ORDER BY [1];
> (There are also some other funny rules and bugs I've seen by using
> constants in ORDER BY, I can dig them up if need be... I think Steve Kass
> has posted a few here.)
> Also, if you are using SELECT * (did I mention this was terrible
> programming practice and opens a can of barracudas?), can you really rely
> on your co-workers to never change the column structure (either
> intentionally or accidentally)?
> It is trivial to generate a column list, either up front or on the fly,
> for any table you are selecting from (especially if you only have to do it
> once, e.g. when you create the view or procedure). So I'm not sure I
> believe that you will be gaining anything by using * and not having to
> know the first column name, because there are a lot of downsides.
>|||"mcnewsxp" <mcourter@.mindspring.com> wrote in message
news:eaZV4KWkGHA.1600@.TK2MSFTNGP04.phx.gbl...
> just lazy i guess.
Famous last words. Be very careful taking the easy/fast way out.
What saves you 10 minutes now, may very well cost you 10 hours later on.
List out all your columns, and specify in every script exactly which column
name you are ordering by. That way when someone changes a table or view, or
adds a column to your select statement, your code will still work.|||Yes, I think that's another danger, but I must confess I would probably find
some of that in my code were I to perform a formal review of the last 5
years of work. ;-)
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:e9x8m5WkGHA.1260@.TK2MSFTNGP05.phx.gbl...
>I think the ORDER BY using an ordinal is getting deprecated in a future
>version, I'm sure I've read it somewhere...|||> You might want to read a book and find out why IDENTITY can *never* be
> a key. By definition. What you are doing is mimicing a 1950's magnetic
> tape file in SQL. The IDENTITY is an exposed physical locator you are
> using, the same way we used record positions on a mag tape.
It can be a SURROGATE KEY without problem.
And, your definition re Codd and Date's work on surrogates is just plain
wrong as well.

> All you will get in Newsgroups are the kludges; you need to get an
> education. And it will take you at least a year to do that. Your
> whole mindset is wrong and you have to unlearn a lot.
Do you even realise how condesending and arrogant you sound?
You have plenty of weaknesses yourself.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1150477719.742027.253030@.i40g2000cwc.googlegroups.com...
> No, you have to sort by something. When do not put the ORDER BY in a
> cursor (it is not part of a SELECT, another common newbie assumption),
> then the engine can out the rows into a sequence in any order. Every
> SQL product will be a bit different, depending on physical storage,
> parallelism in the hardware, etc.
>
> You might want to read a book and find out why IDENTITY can *never* be
> a key. By definition. What you are doing is mimicing a 1950's magnetic
> tape file in SQL. The IDENTITY is an exposed physical locator you are
> using, the same way we used record positions on a mag tape.
>
> No. This is the definition of a table -- it is a set without any
> physical ordering. When you finally read a book on RDBMS, pay
> attention to "The Information Prinicple" and some of the other rules
> that Dr. Codd set up.
> There are some proprietary kludges you can use to destroy portability
> and data integrity. For example, there is a ordinal position number
> that was removed from Standard SQL a few years ago, but exists in some
> products.
> All you will get in Newsgroups are the kludges; you need to get an
> education. And it will take you at least a year to do that. Your
> whole mindset is wrong and you have to unlearn a lot.
>