Maarten Maarten - 22 days ago 6
SQL Question

SQL table ORDER BY (empty fields at bottom)

Good morning, I have a question about ordering my SQL table. I've already tried several things but I can't find the solution that I want.

My table looks as follows:

username childs+pets childs pets
=======================================
Max 1 1
Nico 3 1 2
Lewis 2 2
Daniel 2 1 1


I want to order my table by childs+pets (ASCending), but I want to put the records with empty fields (Max and Lewis) at the bottom of the table. Which results in:

username childs+pets childs pets
=======================================
Nico 3 1 2
Daniel 2 1 1
Lewis 2 2
Max 1 1


Who can help me? Many thanks in advance!

Answer

This is a solution that works in SQL Server. I've also assumed that Childs+Pets is a calculated field from the two separate fields.

Test Data;

CREATE TABLE #TestData (Username nvarchar(10), Childs int, Pets int)
INSERT INTO #TestData (Username, Childs, Pets)
VALUES
('Max',NULL,1)
,('Nico', 1,2)
,('Lewis',2,NULL)
,('Daniel',1,1)

Query

SELECT
    td.Username
    ,COALESCE(td.Childs,0) + COALESCE(td.Pets,0) Childs_Pets --The coalesce returns a Zero if the field contains a NULL
    ,td.Childs
    ,td.Pets
FROM #TestData td
ORDER BY CASE WHEN td.Childs IS NULL OR td.Pets IS NULL THEN 0 ELSE 1 END DESC
,COALESCE(td.Childs,0) + COALESCE(td.Pets,0) ASC

Output

Username    Childs_Pets     Childs      Pets
Daniel      2               1           1
Nico        3               1           2
Max         1               NULL        1
Lewis       2               2           NULL

The CASE statement gets ordered first so if anything has a NULL value in either Childs or Pets then it will be pushed to the bottom. The ordering of Childs_Pets comes after and sorts ASC as you wanted.