sorting order ... 1

[tran008]

The following query is support to sort the state out in order, but I guess I must have been missing something. Can someone explain why this is not working and what I can do to resolve this.

thanks

--trying to sort them out in alpha
select isnull(clmstate,''),count(isnull(clmstate,'')) from claims
Where clmstate NOT IN ('','NO') group by clmstate order by clmstate

union all
--if not in the state add them in bottom of the rows
select isnull(clmstate,''),count(isnull(clmstate,'')) from claims
Where clmstate IN ('','NO') group by clmstate order by clmstate

 

[SQLSister]

please give some sample data and what you expected to get in the sort and waht you got.

Questions about posting. See faq183-874

 

[tran008]

Hi Sis,

There are not much in the data, I just want to sort the States List out. The problem is the the following statement work fine.

select isnull(clmstate,''),count(isnull(clmstate,'')) from claims Where clmstate NOT IN ('','NO') group by clmstate
union all
select isnull(clmstate,''),count(isnull(clmstate,'')) from claims Where clmstate IN ('','NO') group by clmstate

But if I add the 'Order By clmstate ' to sort out the STATES List Name, I get the follow error:
Incorrect syntax near the keyword 'union'.

 

[gmmastros]

Try this...

[COLOR=blue]Select[/color] State, ClaimCount
[COLOR=blue]From[/color]   (
       [COLOR=blue]select[/color] [COLOR=#FF00FF]isnull[/color](clmstate,[COLOR=red]''[/color]) [COLOR=blue]As[/color] state,
              [COLOR=#FF00FF]count[/color]([COLOR=#FF00FF]isnull[/color](clmstate,[COLOR=red]''[/color])) [COLOR=blue]As[/color] ClaimCount,
              1 [COLOR=blue]As[/color] OrderBy
       [COLOR=blue]from[/color]   claims 
       [COLOR=blue]Where[/color]  clmstate NOT IN ([COLOR=red]''[/color],[COLOR=red]'NO'[/color]) 
       [COLOR=blue]group[/color] [COLOR=blue]by[/color] clmstate 

       union all

       [COLOR=blue]select[/color] [COLOR=#FF00FF]isnull[/color](clmstate,[COLOR=red]''[/color]),
              [COLOR=#FF00FF]count[/color]([COLOR=#FF00FF]isnull[/color](clmstate,[COLOR=red]''[/color])),
              2 [COLOR=blue]As[/color] OrderBy
       [COLOR=blue]from[/color]   claims 
       [COLOR=blue]Where[/color]  clmstate IN ([COLOR=red]''[/color],[COLOR=red]'NO'[/color]) 
       [COLOR=blue]group[/color] [COLOR=blue]by[/color] clmstate 
       ) [COLOR=blue]As[/color] A
[COLOR=blue]Order[/color] [COLOR=blue]By[/color] OrderBy, State

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom