DESC in SQLite conditional ORDER BY

Question

I need to select records ordered by the following logic, however SQLite raises an error when DESC is in the conditional.

ORDER BY
CASE 
  WHEN parentGUID IS NULL THEN datePosted DESC
  ELSE datePosted
END

This is to achieve Facebook like ordering =- original posts (which always have null parentGUIDs) in order descending by date and replies to original posts ordered by date ascending.

Answer 1

If I understand you right, you'll need to join to the table that has the date of the parent post. If that's available, something like this should do:

DECLARE @X TABLE
(
ID  INT NOT NULL IDENTITY PRIMARY KEY,
parentID INT,
datePosted DATE NOT NULL
)
INSERT INTO @X (parentID, datePosted) VALUES
    (NULL, '2010-01-01'),
    (NULL, '2010-01-02'),
    (1,    '2010-01-03'),
    (1,    '2010-01-04'),
    (1,    '2010-01-05'),
    (2,    '2010-01-06')

SELECT
    Post.parentID, Post.datePosted
FROM @X AS Post
    LEFT JOIN @X AS Parent ON Post.parentID = Parent.ID
ORDER BY
    -- Order by post date, or rather the parent's post date if one exists
    COALESCE(Parent.datePosted, Post.datePosted)
    -- Order by reply date
    Post.datePosted

This gives this output:

parentID datePosted
-------- ----------
NULL     2010-01-02
2        2010-01-06
NULL     2010-01-01
1        2010-01-03
1        2010-01-04
1        2010-01-05

Note that this will break if replies can have replies in turn; you'd need something more robust. In MS SQL, I'd use a CTE, but I'm not very familiar with Sqlite.

Answer 2

With your correction, I think I now understand. Here's some sample data

|GUID|parentGUID|datePosted|
+----+----------+----------+
|1   |null      |2010-01-01|
|2   |null      |2010-01-02|
|3   |1         |2010-01-03|
|4   |1         |2010-01-04|
|5   |1         |2010-01-05|
|6   |2         |2010-01-06|

And I'm guessing you want this output:

|GUID|parentGUID|datePosted|
+----+----------+----------+
|1   |null      |2010-01-01|
|5   |1         |2010-01-05|
|4   |1         |2010-01-04|
|3   |1         |2010-01-03|
|2   |null      |2010-01-02|
|6   |2         |2010-01-06|

That's quite hard (and probably slow) to order in a single query