T-SQL ORDER BY Bug? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

T-SQL ORDER BY Bug?

Author

Topic

mellamokb
Starting Member

39 Posts

Posted - 2007-08-14 : 11:59:30

Hi,

I am having a problem with this query (an excerpt of a larger query):


SELECT ItemName AS _order, NULL AS ItemName
FROM tblItem
ORDER BY _order, ItemName

It generates the following error:


Server: Msg 169, Level 15, State 2, Line 34
A column has been specified more than once in the order by list. Columns in the order by list must be unique.

The problem seems to arise because I am giving a field value (ItemName) an alias (_order), then giving another field value (NULL) the alias of the other field (ItemName). However, there is no interdependency between fields like MS Access, i.e., you cannot alias one field and then use the alias later to build another field:


SELECT 123 AS Expr1, Expr1 + 255 AS Expr2, Expr2 - 5 AS Expr3

In MS Access Expr3 would equal 255+123-5, in T-SQL it would generate an error. In the previous query I gave, if I supply a table name, the error disappears:


SELECT tblItem.ItemName AS _order, NULL AS ItemName
FROM tblItem
ORDER BY _order, ItemName

I don't understand why this all happens unless the T-SQL engine seems to understand the concept of interdependent fields like MS Access even though it doesn't exist in MS SQL.

Thanks in advance for your help in understanding this.

~ mellamokb

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-14 : 12:12:35

I don't get any error here

USE NORTHWIND

GO

SELECT orderid AS _order, NULL AS ItemName
FROM orders
ORDER BY _order, ItemName

Ashley Rhodes

Hillside
Starting Member

23 Posts

Posted - 2007-08-14 : 12:17:02

Hi,

You should use the table name and You were trying select from "tblItem" which not table.

mellamokb
Starting Member

39 Posts

Posted - 2007-08-14 : 12:17:13

No, you have to use fields that exist for both places. This gives me an error, even though it should be able to run just fine:


USE NORTHWIND 

GO

SELECT orderid AS _order, NULL AS orderid
FROM orders
ORDER BY _order, orderid

Note the use of "orderid" in two places in the SELECT.

~ mellamokb

mellamokb
Starting Member

39 Posts

Posted - 2007-08-14 : 12:18:43

Hillside, ashley.sql was using NorthWind. I wasn't. My database does have a tblItem.

~ mellamokb

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-14 : 13:51:11

Ofcourse the above will give u this error

Server: Msg 169, Level 15, State 2, Line 4
A column has been specified more than once in the order by list. Columns in the order by list must be unique.

Can you not select Null as tblitem1 or something else other than the column name you are already selecting

Why are you trying to do this.

Ashley Rhodes

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-14 : 14:00:09

You can always use the column number

ORDER BY 1,2

Jim

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-14 : 15:22:02

problem is not in order by its in the select list

Ashley Rhodes

mattyblah
Starting Member

49 Posts

Posted - 2007-08-14 : 22:05:58

Try this:


SELECT orderid AS _order, 'orderid'=NULL
FROM orders
ORDER BY _order, orderid

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-08-15 : 07:20:38

My question is, why are you aliasing out a column as the name of an existing column?
Why not

SELECT ItemName AS _order, 'NameOfItem' = NULL
FROM tblItem
ORDER BY _order, NameOfItem

Also, since 'NameOfItem' is always NULL, there is no reason to include it in the ORDER BY clause.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

mellamokb
Starting Member

39 Posts

Posted - 2007-08-18 : 10:53:30

Hi,

The excerpt is from a very large, complicated, programmatically-generated query that is used to create a report with grouping levels. The purpose of that naming convention is to simplify the process of identifying which column(s) is/are sorted. I have been able to get around this problem by specifying the table name rather than just the field name, i.e., ORDER BY tblItem.ItemName instead of ORDER BY ItemName.

I think the reason it is not working is because you *cannot* reference aliases anywhere else in a T-SQL query. Regardless of how I name the columns, ItemName and _order will always both refer to the first column (ItemName AS _order), not the second column (NULL AS ItemName) because ItemName always refers to the column in the database, not the aliased column name.

Thank you for your help and suggestions.

~ mellamokb

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-18 : 12:00:07

one way to get around that is to simply do this:

select * from
(
your complex query here
) t
order by ... <- you can put your aliases here

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

uday.sql
Starting Member

3 Posts

Posted - 2007-08-23 : 19:19:02

Since you that ItemName is NULL in the first place, you do not need to include in the ORDER BY.

If you still need it,

SELECT ItemName AS _order, NULL AS ItemName
FROM tblItem
ORDER BY _order, NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources