Grimm Grimm - 3 months ago 12
Vb.net Question

Arrange sorting type of asc in stored procedure

I have a data in my database like this:

0 1 2 1 0 3
and i want to sort it ascending and the 1st record should be 1 not the 0. I want an output like this:

1 1 2 3 0 0
.

Please any one help me? i have a stored procedure like this:

select * from table order by number ascending


and the output is
0 0 1 1 2 3


anyone?

Answer
select * from table order by CASE WHEN id = 0 THEN 999999 ELSE id END 

This simply changes the sort order and is easily extensible if you want to add extra fields.