How to get an ordered list of rows within 0 value whit sql?

Question

For example:

---

SELECT * FROM atable ORDER BY num; 

---

'atable' is:

---

num  name     
1     a    
3     y    
0     cc    
2     fs

---

The result is:

---

num  name     
1     a    
2     fs    
3     y    
0     cc

---

But I want it to be:

---

num  name  
0     cc    
1     a    
2     fs    
3     y

Answer 1

SELECT * FROM atable 
ORDER BY ISNULL(CAST(num as int), 0); 

Answer 2

Are you sure that the 0 isn't a null value being displayed as a 0? Nulls can sort either at the top or the bottom, depending on database setting.

Answer 3

I can't reproduce the result you are seeing. The query that you posted should work as you wish it to. Here's my steps to reproduce:

CREATE TABLE atable (num INT NOT NULL, name NVARCHAR(100) NOT NULL);
INSERT INTO atable (num, name) VALUES
(1, 'a'),
(3, 'y'),
(0, 'cc'),
(2, 'fs');
SELECT * FROM atable ORDER BY num;

Result:

0, 'cc'
1, 'a'
2, 'fs'
3, 'y'

Perhaps you could post your create scripts for your table and test data in your question so that we can reproduce your result?