get hight nationality

Question

User66371569 posted

Hi

i created some query which get high nationalities 

select nat,count(*) as count from nationalists group by  nat order count

query work  perfect 

Us  1000

uk 500

Indan 400

what i want  is put Indian first row in query then  other nationalities order by count     how to do that

Answer 1

User364663285 posted

Try

select 'Indian' nat,count(*) as count0 from nationalists where Nat='Indian'
union all select * from (select nat,count(*) as count0 from nationalists group by nat having Nat<>'Indian' order by count0) a

Answer 2

User-943250815 posted

Adding an ID column

SELECT case when nat='Indian' then 0 else 1 end as ID,
       nat, 
       count(*) as Qty,
FROM <table>
GROUP BY nat
ORDER BY ID, QTY

Answer 3

User77042963 posted

SELECT  nat, count(*) as [count]
FROM nationalists 
GROUP BY nat
ORDER BY case when nat='Indian' then 0 else 1 end, [count] DESC