Seeder question: order by error?

Question

question

Kev Riley asked Mar 4, '10

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-sql seeder-question

3

10 |1200

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

Kev Riley ♦♦ commented · Mar 04, 2010 at 01:28 PM

Yup! 7 days for 2k rep users to stay clear - discussed here http://ask.sqlservercentral.com/questions/3402

1 ·

Tom Staab ♦ commented · Mar 04, 2010 at 01:09 PM

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 ·

Ian Roke commented · Mar 04, 2010 at 01:24 PM

Was that agreed? I don't think it is really an issue is it?

0 ·

Answer 1 · 2010-03-04T13:09:18Z

sp_lock answered Mar 4, '10

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

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

Answer 2 · 2010-03-04T13:10:58Z

CirqueDeSQLeil answered Mar 4, '10

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

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

question