How to filter?

DanielLiljeberg

I have a table that amongst other things include "id" and "reply_to".
The "id" is the unique id of a thread and "reply_to" is a INTEGER of the "id" of the thread you have replied to. Now, I would like to pick the 10 latest threads with posts. But if I do "SELECT * FROM news ORDER BY time DESC LIMIT 10" I only get the ten latest posts. Since there could be three posts in a row in the same thread I would need to do something like select distinct but filter on "id" and "reply_to" so that if and "id" has already appeared in a previous "id" or "reply_to" feild it's skipped. How do I write this?

Sxooter

I'm thinking of starting with some kind of join of the table or something, against itself. But I'm not sure how I'd do it right off the top of my head. We're having a wine tasting at work right now and I'm not at my most lucid. I'll think about it a bit and try it later when I'm not quite so tipsy.

something like:

select a.id, a.reply_id, b.id, b.reply_id from table a join table b on (a.id=b.reply_id)

and wrapping that in an outer select with a distinct.

DanielLiljeberg

Looking forward to you sobering up then 😉

Lars_Berg

SELECT * 
FROM threads t
where time in
(select max(time)
   from threads
  where t.reply_to = threads.reply_to)
ORDER BY time DESC 
LIMIT 10