SQL question

[Anon]

I'm writing a little message board.

There are 3 types of messages. Normal, Permanent, and Announcement. I want the announcements to be at the very top, Permanent messages are listed below those, and Normal messages are listed below those, so the post listing is like this..

Anounncements
Permanent
Normal

And also, I want to order them by the time they were posted (in descending order). But I can't get my query to work, my query will list things in random order! Here's my query so far...

SELECT * FROM article WHERE thread_type = 'PERMANENT' OR thread_type = 'NORMAL' OR thread_type = 'ANNOUNCEMENT' ORDER BY (thread_type = 'ANNOUNCEMENT OR thread_type = 'PERMANENT' OR thread_type = 'NORMAL') AND thread_lastdate ASC

Any suggestions for fixing this?

Thanks in advance!!
James

[Anon]

Weeeell, if you're using a database that supports unions, then just union your three queries together.

Chris

[Anon]

SELECT * FROM article
WHERE thread_type in ('PERMANENT','NORMAL', 'ANNOUNCEMENT' )
ORDER BY case thread_type
when 'ANNOUNCEMENT' then 1
when 'PERMANENT' then 2
when 'NORMAL' then 3 end,
thread_lastdate desc