question

Kev Riley avatar image
Kev Riley asked

Seeder question: order by error?

Seeder question

I am trying to query a table, returning all the columns, and order by the fourth column (but show this first), however I am getting this error. What have I done wrong?

declare @somedata table
(
    col1 int,
    col2 int,
    col3 int,
    col4 int
)

insert into @somedata select 1,1,1,1
insert into @somedata select 2,2,2,2
insert into @somedata select 3,3,3,3
insert into @somedata select 4,4,4,4
insert into @somedata select 5,5,5,5
insert into @somedata select 6,6,6,6

select col4,* from @somedata
order by col4

gives me the error

Msg 209, Level 16, State 1, Line 16
Ambiguous column name 'col4'.

Edit > As well as workarounds, I would like to know why the error occurs too!

t-sqlseeder-question
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yup! 7 days for 2k rep users to stay clear - discussed here http://ask.sqlservercentral.com/questions/3402
1 Like 1 ·
Just for clarification, did we agree that users with reputation over 2,000 will not answer seeder questions until others have had some time? a few days?
0 Likes 0 ·
Was that agreed? I don't think it is really an issue is it?
0 Likes 0 ·
sp_lock avatar image
sp_lock answered

You can either fully qualify the columns or alias Col4

declare @somedata table
(
    col1 int,
    col2 int,
    col3 int,
    col4 int
)

insert into @somedata select 1,1,1,1
insert into @somedata select 2,2,2,2
insert into @somedata select 3,3,3,3
insert into @somedata select 4,4,4,4
insert into @somedata select 5,5,5,5
insert into @somedata select 6,6,6,6

select col4 as col4b, * from @somedata 
order by col4b

In prod I would personally not use the SELECT *

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

CirqueDeSQLeil avatar image
CirqueDeSQLeil answered

Unless you need to show col4 twice, I would eliminate the *.

Easiest solution for me is:

select col4,col1,col2,col3
from @somedata
order by col4
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.