[RESOLVED] LEFT JOIN and ORDER BY problem

kemu_

Im having a sorting problem with our intranet news-system. It has the actual news and replies stored in different tables, and i would like it to show the latest piece of news or the one that has the latest reply first (like in here). But the problem comes when i try to order the query.

SELECT * from news LEFT JOIN replies ON replies.news_id=news.id ORDER BY replies.date DESC, news.date DESC

Problem lies with the fact that not all news have any replies, so the database returns null as replies.date for those, which is sorted before any "real" date. Is there some way i could "set" those null fields as something like 1.1.1900 or so? or is there some way to get around it? Im using postgre 8... I could do the sorting in PHP i guess, but it gets a lot more complicated that way...

Roger_Ramjet

If you labelled this thread as a postgres problem Sxooter might have answered - he's the resident postgres expert around here.

I say this because the way to solve this problem in mysql would be to use a Control Flow Function like IFNULL to substitute for the NULLs. I don't know what functions postgres has to do this with; take a look at the documentation for it.

Sxooter

OK, got an answer. it's a case statement, and since case is bog standard sql, it should work with ANY sql database.

select 
    *, 
    case when 
        r.dt is null 
        then '1900-01-01'::date 
    else 
        r.dt 
    end as fakedate 
from 
    news n 
left join 
    replies r 
on 
    (r.news_id=n.id) 
order by 
    fakedate desc;

Note that since the word date is a reserved word, I changed it to dt in my example.

tomhath

What Sxooter posted works. A couple of other ways to do it are with the ISNULL or COALESCE keywords.

When the problem is in the ORDER BY, I usually change the values there. Something like this (untested).

SELECT * from news 
LEFT JOIN replies ON replies.news_id=news.id 
ORDER BY ISNULL(replies.date, '1900-01-01') DESC, news.date DESC

kemu_

/me bows to Sxooter, worked like a charm 🙂