Bxdty

I would like to order this table but I can not find a way to make it work. Can you help me?

I have this table:

I need this:

I have a table where documents are listed, the first field is the id of the document and the second field represents the father, so I must show a list where you can see in an orderly manner that 242 is the first document and 252 and 335 were generated from the 242.

The query:

select * from table_name order by col1 ASC, Col2 ASC

...doesn't work for me. I do this query:

SELECT

    FR1.[report_id],

    FR1.[report_parent] 

FROM [FARA_reports] FR1

WHERE

    FR1.[report_is_delete] <> 1 

    AND FR1.[report_is_tmp] <> 1

ORDER BY

    FR1.[report_id] asc, 

    FR1.[report_parent] desc

and this is my first image.

Another option:

select a, b

from t

order by 

    case when a < b or b is null then a else b end,

    case when a < b or b is null then b else a end ;

Test in dbfiddle.uk.

Your data:

CREATE TABLE dbo.WEIRD_ORDERING

(

ID1 INT NULL,

ID2 INT NULL

);



INSERT INTO dbo.WEIRD_ORDERING VALUES

(242, NULL),

(243, NULL),

(244, NULL),

(252, 242),

(254, NULL),

(255, NULL),

(256, NULL),

(292, NULL),

(308, NULL),

(311, NULL),

(313, 311),

(314, 311),

(323, NULL),

(324, 311),

(335, 242),

(340, NULL),

(341, NULL),

(358, NULL),

(372, NULL),

(373, NULL),

(377, NULL),

(378, 358),

(379, 358),

(380, 358),

(381, 358);

This gives you results in the desired order:

SELECT ID1, ID2

FROM

(

    SELECT ID1, ID2, ID1 AS ORDERING_COLUMN

    FROM dbo.WEIRD_ORDERING

    WHERE ID1 <= ID2 OR ID2 IS NULL



    UNION ALL



    SELECT ID1, ID2, ID2 AS ORDERING_COLUMN

    FROM dbo.WEIRD_ORDERING

    WHERE ID1 > ID2 OR ID1 IS NULL

) q

ORDER BY ORDERING_COLUMN;

db fiddle

Stealing the sql fiddle table from Joe Obbish this is a simple solution

SELECT * FROM dbo.WEIRD_ORDERING

ORDER BY coalesce(ID2*1000+1, ID1*1000)

SELECT *

FROM datatable

ORDER BY CASE WHEN field2 IS NULL THEN field1 ELSE field2 END, -- sort groups

         CASE WHEN field2 IS NULL THEN 1 ELSE 2 END, -- move parent first

         field2 -- sort childs

All answers so far rely on an assumption that there are no further levels of "generated from" (e.g. there is no 382 generated from the 381 which is itself generated from 352 - or which was it).

They should know what is said of "assume", so they should have asked you about this first.

If you can indeed have further levels of "generated from", then you need a recursive query to do the full path construction ("352" , "352/381" , "352/381/382" , etc etc) and ORDER BY that full path.