order by help - 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 2005 Forums

Transact-SQL (2005)

order by help

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-08-08 : 01:56:01

select catC.catid, catP.categoryname + '->' + catC.categoryname as categoryname from category catC INNER JOIN category catP on catP.catID = catC.parentcatid order by categoryname

above is my sql

how can I get it to order by first CatP.categoryname and then CatC.categoryname ?

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-08-08 : 02:12:22

ORDER BY CatP.categoryname, CatC.categoryname

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-08-08 : 02:41:02

i get an error

A column has been specified more than once in the order by list. Columns in the order by list must be unique.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-08 : 02:46:46

quote:
Originally posted by esthera

i get an error

A column has been specified more than once in the order by list. Columns in the order by list must be unique.

Can you post the code you used?

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-08-08 : 02:54:43

select catC.catid, catP.categoryname + '->' + catC.categoryname as categoryname from category catC INNER JOIN category catP on catP.catID = catC.parentcatid ORDER BY CatP.categoryname, CatC.categoryname"

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-08-08 : 03:46:18

Are you using SQL Server?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-08-08 : 04:05:46

yes

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-08-08 : 04:15:55

Maybe you try using table alias.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-08 : 04:18:40

quote:
Originally posted by esthera

select catC.catid, catP.categoryname + '->' + catC.categoryname as categoryname from category catC INNER JOIN category catP on catP.catID = catC.parentcatid ORDER BY CatP.categoryname, CatC.categoryname"

Are you sure you are using the same query? It is correct syntax
Check again if you have missed to specify proper table alias

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-08-08 : 04:22:32

this is the exact query

i didn't use alias

select catC.catid, catP.categoryname + '->' + catC.categoryname as categoryname from category catC INNER JOIN category catP on catP.catID = catC.parentcatid ORDER BY CatP.categoryname, CatC.categoryname"

they are both categoryname -- should i try to make an alias?

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-08-08 : 04:30:04

I'm not quite sure why but the following seems to work.
select catC.catid, catP.categoryname + '->' + catC.categoryname as category_name from category catC INNER JOIN category catP on catP.catID = catC.parentcatid ORDER BY CatP.categoryname, CatC.categoryname

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-08-08 : 04:59:48

that worked - -thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources