sql - DESC in SQLite conditional ORDER BY -

i need select records ordered following logic, sqlite raises error when desc in conditional.

order case    when parentguid null dateposted desc   else dateposted end 

this achieve facebook ordering =- original posts (which have null parentguids) in order descending date , replies original posts ordered date ascending.

if understand right, you'll need join table has date of parent post. if that's available, should do:

declare @x table ( id  int not null identity primary key, parentid int, dateposted date not null ) insert @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 @x post     left join @x parent on post.parentid = parent.id order     -- order post date, or rather parent's post date if 1 exists     coalesce(parent.dateposted, post.dateposted)     -- order reply date     post.dateposted 

this gives 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 break if replies can have replies in turn; you'd need more robust. in ms sql, i'd use cte, i'm not familiar sqlite.