Surprising - SQL Quey - order by !!

[TimHop12]

Hi all,

The query below is not compiling. Please help:

Select case
                when isNull(a.[USER_ID],0)=0 Then char(43)
                when isNull(t.[USER_ID],0)=0 Then char(45)
                else char(160)
        end as ind, count(*)
from T1 t
left outer join T2 a on
                t.ASSIGNMENT_ID = a.ASSIGNMENT_ID
group by ind
order by ind

[iburyak]

You can do following ways:

1.

Select case
                when isNull(a.[USER_ID],0)=0 Then char(43)
                when isNull(t.[USER_ID],0)=0 Then char(45)
                else char(160)
        end as ind, count(*)
from T1 t
left outer join T2 a on
                t.ASSIGNMENT_ID = a.ASSIGNMENT_ID
group by case
                when isNull(a.[USER_ID],0)=0 Then char(43)
                when isNull(t.[USER_ID],0)=0 Then char(45)
                else char(160)
        end
order by 1

2.

Select ind, count(*) 
FROM (
    Select case
                    when isNull(a.[USER_ID],0)=0 Then char(43)
                    when isNull(t.[USER_ID],0)=0 Then char(45)
                    else char(160)
            end as ind
    from T1 t
    left outer join T2 a on
                    t.ASSIGNMENT_ID = a.ASSIGNMENT_ID) a
group by ind
order by ind

Good Luck.

[TimHop12]

Thanks a lot iburyak!

This helped...

[TimHop12]

Thanks a lot iburyak. This helped...

But please help me understanding, why the original query is not working? Where I am missing at. Appreciate your help... Thank you.

[porter]

TimHop12,

Just want to share may idea why your codes didn't work, because you cant group by a "alias" name in the group statement. on the other hand, if you want to use the "alias" name, you should do a select statement in another select statement.

[iburyak]

TimHop12,

See Porter's respond it is exactly what it is.

If you need more clarification let us know and we will explain in more details.


Good Luck.