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.
>

ORDER BY and IDENTITY

Well, it seems like your persistency was worthwhile!
I got a reply from Microsoft which says that technique 2 (INSERT SELECT into
table with IDENTITY) is in fact guaranteed, while all the others aren't.
Cheers,
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Questar" <Questar@.newsgroup.nospam> wrote in message
news:EDC4A261-24F6-4891-9EBA-DE081E31B810@.microsoft.com...
> Thanks for the follow-up!
> "Itzik Ben-Gan" wrote:
>Interesting, I will definitely keep it in mind. Thanks for all your
efforts. It would definitely be worth an INFO article in Microsoft's
knowledge base...
Gert-Jan
Itzik Ben-Gan wrote:
> Well, it seems like your persistency was worthwhile!
> I got a reply from Microsoft which says that technique 2 (INSERT SELECT in
to
> table with IDENTITY) is in fact guaranteed, while all the others aren't.
> Cheers,
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> "Questar" <Questar@.newsgroup.nospam> wrote in message
> news:EDC4A261-24F6-4891-9EBA-DE081E31B810@.microsoft.com...|||I completely agree. I know I'm planning to write one. ;-)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41F56391.55685A2D@.toomuchspamalready.nl...
> Interesting, I will definitely keep it in mind. Thanks for all your
> efforts. It would definitely be worth an INFO article in Microsoft's
> knowledge base...
> Gert-Jan
>
> Itzik Ben-Gan wrote:|||Thank you very much for your role in finding an answer!
"Itzik Ben-Gan" wrote:

> Well, it seems like your persistency was worthwhile!
> I got a reply from Microsoft which says that technique 2 (INSERT SELECT in
to
> table with IDENTITY) is in fact guaranteed, while all the others aren't.
> Cheers,
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Questar" <Questar@.newsgroup.nospam> wrote in message
> news:EDC4A261-24F6-4891-9EBA-DE081E31B810@.microsoft.com...
>
>|||I just ran across this thread and I wanted to share my experiences.
Where I work, we are often performing very large data-mining type of
queries. Often on the order of 100's of millions of rows. Using INSERT
statement for result sets are much to slow. So we normally use SELECT
INTO based table creation.
Since our processing often requires the creation of an ordered identity
column to process the data we have had to try to find solutions that
will allow for SELECT INTO using the IDENTITY function with and ORDER BY
clause. We tried using MAXDOP 1 as a hint to fix the problem and found
that unreliable.
Then we tried using derived tables using an ORDER BY clause with a TOP
100 PERCENT call. This seemed to often fix the problem but not reliably.
Our final solution that seems to work for us is to use derived table
with MAXDOP 1.
Here is an example:
set nocount on
use tempdb
go
drop table ta, tb, #t, #t2
go
create table ta(a int not null)
insert into ta values(3)
insert into ta values(1)
insert into ta values(2)
create table tb(b int not null primary key)
insert into tb values(4)
insert into tb values(1)
insert into tb values(3)
insert into tb values(2)
select b, identity(int, 1, 1) as rn
into #t
from ta join tb on a = b
order by b
select * from #t order by b
b rn
-- --
1 2
2 3
3 1
select b,
identity(int, 1, 1) as rn
into #t2
from (Select top 100 percent
*
from ta
inner join tb
on a = b
order by b) lu
order by b
option (maxdop 1)
select * from #t2 order by b
b rn
-- --
1 1
2 2
3 3
If you try this you will most likely get the same answer with or without
using the MAXDOP hint. I have found that when joining multiple tables
that have clustered indexes require the MAXDOP hint
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Any of these results are undefined so there is some level of risk if
you rely on these behaviours in a production environment. It may appear
to work most of the time, it may even work ALL of the time, but it
could still break under a future schema change, new product version,
service pack or hotfix. You have to decide if the benefit you derive
from this method is worth the risk of any possible future impact. Since
the risk is very hard to quantify I would tend to avoid it unless the
potential impact was quite small.
Here's what Microsoft says:
"Even though this is not guaranteed, there are scenarios where you
might see the IDENTITY function generating identity values in the same
order as defined by the ORDER BY column. This is purely coincidental
and should not be considered the expected behavior."
http://support.microsoft.com/defaul...kb;en-us;273586
--
David Portas
SQL Server MVP
--|||I would agree with completely on this issue. I often find myself running
different tests over and over each time a service pack or hotfix is
installed. Unfortunately SQL SERVER's behavior is sometimes
unpredictable when trying to execute certain types of queries. This is
just one of those types.
Regarding the queries I posted earlier I am including the showplan_all
report.
select b, identity(int, 1, 1) as rn
into #t
from ta join tb on a = b
order by b
StmtText
---
---
--
|--Table Insert(OBJECT:([#t]), SET:([#t].[rn]=[Expr1005],
[#t].[b]=[tb].[b]))
|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE:([Expr1005]=setidentity([E
xpr1004],
-7, 0, '#t')))
|--Sort(ORDER BY:([tb].[b] ASC))
|--Compute
Scalar(DEFINE:([Expr1004]=getidentity(-7, 0, '#t')))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([ta].[a]))
|--Table
Scan(OBJECT:([tempdb].[dbo].[ta]))
|--Clustered Index
S(OBJECT:([tempdb].[dbo].[tb].[PK__tb__29BF9C6D]),
SEEK:([tb].[b]=[ta].[a]) ORDERED FORWARD)
---
select b,
identity(int, 1, 1) as rn
into #t2
from (Select top 100 percent
*
from ta
inner join tb
on a = b
order by b) lu
order by b
option (maxdop 1)
StmtText
---
---
--
|--Table Insert(OBJECT:([#t2]), SET:([#t2].[rn]=[Expr1005],
[#t2].[b]=[tb].[b]))
|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE:([Expr1005]=setidentity([E
xpr1004],
-7, 0, '#t2')))
|--Compute Scalar(DEFINE:([Expr1004]=getidentity(-7, 0,
'#t2')))
|--Sort(ORDER BY:([tb].[b] ASC))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([ta].[a]))
|--Table
Scan(OBJECT:([tempdb].[dbo].[ta]))
|--Clustered Index
S(OBJECT:([tempdb].[dbo].[tb].[PK__tb__29BF9C6D]),
SEEK:([tb].[b]=[ta].[a]) ORDERED FORWARD)
As you can see in the second query, the getidentity function is called
after the sort. Unlike the first query where the getidentity is called
prior to the sort.
In the end, if we can find a repeatable process that saves us hours over
using logged transactions, we will. As it is now, I have processes that
run for 10+ days. So save even a few percent in processing time is very
important.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||I would agree with completely on this issue. I often find myself running
different tests over and over each time a service pack or hotfix is
installed. Unfortunately SQL SERVER's behavior is sometimes
unpredictable when trying to execute certain types of queries. This is
just one of those types.
Regarding the queries I posted earlier I am including the showplan_all
report.
select b, identity(int, 1, 1) as rn
into #t
from ta join tb on a = b
order by b
StmtText
---
---
--
|--Table Insert(OBJECT:([#t]), SET:([#t].[rn]=[Expr1005],
[#t].[b]=[tb].[b]))
|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE:([Expr1005]=setidentity([E
xpr1004],
-7, 0, '#t')))
|--Sort(ORDER BY:([tb].[b] ASC))
|--Compute
Scalar(DEFINE:([Expr1004]=getidentity(-7, 0, '#t')))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([ta].[a]))
|--Table
Scan(OBJECT:([tempdb].[dbo].[ta]))
|--Clustered Index
S(OBJECT:([tempdb].[dbo].[tb].[PK__tb__29BF9C6D]),
SEEK:([tb].[b]=[ta].[a]) ORDERED FORWARD)
---
select b,
identity(int, 1, 1) as rn
into #t2
from (Select top 100 percent
*
from ta
inner join tb
on a = b
order by b) lu
order by b
option (maxdop 1)
StmtText
---
---
--
|--Table Insert(OBJECT:([#t2]), SET:([#t2].[rn]=[Expr1005],
[#t2].[b]=[tb].[b]))
|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE:([Expr1005]=setidentity([E
xpr1004],
-7, 0, '#t2')))
|--Compute Scalar(DEFINE:([Expr1004]=getidentity(-7, 0,
'#t2')))
|--Sort(ORDER BY:([tb].[b] ASC))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([ta].[a]))
|--Table
Scan(OBJECT:([tempdb].[dbo].[ta]))
|--Clustered Index
S(OBJECT:([tempdb].[dbo].[tb].[PK__tb__29BF9C6D]),
SEEK:([tb].[b]=[ta].[a]) ORDERED FORWARD)
As you can see in the second query, the getidentity function is called
after the sort. Unlike the first query where the getidentity is called
prior to the sort.
In the end, if we can find a repeatable process that saves us hours over
using logged transactions, we will. As it is now, I have processes that
run for 10+ days. So save even a few percent in processing time is very
important.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Oracles ROWNUM equivalent in SQL Server

Hi,

Can any one tell me is there anything in SQL Server thats equivalent
to Oracle's ROWNUM.

Note that the Identity Property or TOP n will not solve my problem.

I want to asign a sequence no. to each row when its being fetched.

For example if in the emp table there are 2000 rows and I write
the following query in Oracle ,

SELECT rownum , empno, empname FROM emp Where rownum < =3

I get the result like this

Rownum--Empno-----Empname
-------------
1----2345-----ABCD
2----3334-----EFGH
3----4484-----IJKL

I know I can limit the output rows in SQL Server by using TOP n. But
I also want to generate a sequence no. The identity property of SQL Server
will not be usefull here because my actaul WHERE clause will be more
complex like WHERE resigndate = '01-jan-2004'

Thanks

Asim Naveed

3A frequently asked question...

The answer usually involves creating a temp table (or table function) with an identity column. Insert the results of your query into this temp table and you'll have more or less what you need.

Hopefully this is something that is addressed in 2005?

hmcott|||There is actually a good reason for this "limitation" to exist. If the result set is being used on the server, it has to go into a table anyway, so the IDENTITY solution works nicely. If the result set is being used on a client, then the client ought to provide the rowid values (since the client knows more about how it wants the ids arranged than the server can).

This is actually a good design property, even though applications being moved from database managers like dBase and Oracle are often dependant on physical details that they shouldn't rely on.

-PatP