Integer order with empty fields below

Question

We are rewriting our CMS at the moment, and we want our customers to be able to reorder items in the table using the "position" field. So, if they mark positions as position 1, it goes up, then position 2 below it, etc.

The problem is that we don’t want them to fill out a position every time, only if they want to reorder something, so the position field will often be blank. Therefore, you may have the following ...

Car - 1

Bike - 2

House

A computer

Dog

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

SELECT ProductName FROM Products ORDER BY Position DESC;

All spaces go up, not down.

Can I put them in the correct order using an SQL statement?

Answer 1

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

, :

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

(, ), ProductName ProductID , .

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

Answer 2

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

:

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

, position , .

Answer 3

.

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

NULL

, "unset" rows integer:

• UPDATE SET Position = 2147483647 WHERE IS NULL
• set NOT NULL column, add default value 2147483647
• use ORDER BY as intended
• in your application or in your download request, turn off the display of any value 2147483647

here is a boot example:

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 zeros, do it like this:

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