Order by integer with blank fields at the bottom.

Question

Hi,

We're rewriting our CMS at the moment and we want our clients to be able to re-order items in a table using a "position" field. So, if they mark an items as position 1 it goes at the top, then position 2 underneath it etc.

The problem is that we don't want to them to have to fill in a position every time, only if they want to re-order something so the position field will often be blank. So you might have the following...

Car - 1

Bike - 2

House

Computer

Dog

This causes a problem because if you use the following SQL...

SELECT ProductName FROM Products ORDER BY Position DESC;

All the blank ones go to the top, not the bottom.

Can they be put into the correct order using a SQL statement?

Answer 1

SELECT  ProductName
FROM    Products
ORDER BY
        COALESCE(position, (SELECT MAX(position) + 1 FROM Products))

Alternatively, you can use:

SELECT  *
FROM    (
        SELECT  ProductName
        FROM    Products
        WHERE   position IS NOT NULL
        ORDER BY
                position
        ) q
UNION ALL
SELECT  ProductName
FROM    Products
WHERE   position IS NULL

This will be more efficient, since the first subquery will use the index on position for ordering is there is one.

Answer 2

SELECT ProductName
FROM Products
ORDER BY
  CASE WHEN Position is null THEN 1 ELSE 0 END,
  Position

If you want to order by Position Descending while maintaining nulls at the bottom, this will do it:

ORDER BY
  CASE WHEN Position is null THEN 1 ELSE 0 END,
  Position desc

If you want consistent sorting (you might be paging), then add ProductName or ProductID on the end to break all the ties between unpositioned products.

ORDER BY
  CASE WHEN Position is null THEN 1 ELSE 0 END,
  Position,
  ProductID

Answer 3

This should do the the job for you.

CREATE TABLE [dbo].[Test](
    [Position] [int] NULL,
    [Title] [varchar](15) NULL
) 
GO

INSERT INTO Test Values(1, 'P1')
INSERT INTO Test Values(2, 'P2')
INSERT INTO Test Values(NULL, 'P3')
INSERT INTO Test Values(NULL, 'P4')
INSERT INTO Test Values(NULL, 'P5')
GO

SELECT Title + ' - ' +  CASE  
    WHEN POSITION IS NULL THEN ''
    ELSE CAST(Position AS CHAR(3))
    END
FROM Test
ORDER BY CASE WHEN Position is null THEN 1 ELSE 0 END

Answer 4

Only use NULLs when necessary

you can easily eliminate the use of nulls, and the resulting sort problem, by defaulting "unset" rows to the integer max value:

• UPDATE Products SET Position=2147483647 WHERE Position IS NULL
• set the column to NOT NULL, add a default value of 2147483647
• use your ORDER BY as intended
• in your app or in your load query, suppress the display of any value of 2147483647

here is a sample load:

SELECT 
    ProductName
        ,CASE
             WHEN Position=2147483647 THEN NULL  --or cast this as a varchar and use 'N/A'
             ELSE Position
         END
    FROM ...

If you must use nulls, do it this way:

SELECT
    ProductName
        ,Position
    FROM Test
    ORDER BY COALESCE(Position,2147483647)