MySQL Query sorting problem, using JOINs and GROUP 2

[JGH]

Hi folks -

I have a forum that uses two tables to maintain the threads and messages and I want to list all the threads in descending order of the most recent post.

Below is the query I have been using. I think the naming makes it obvious what's what, but basically there is a table called "Thread" that contains column "id" and some other stuff not relevant here. And there is a table called "Msg" with id, threadid (referring to Thread's id column), date, and so on. All Msg rows refer to some Thread.id.

I'm not well-versed on using GROUP BY, especially with JOINed tables. So I defer to the experts here.

The incorrect query:

SELECT DISTINCT Thread.id, count(*) as msgcount
FROM Thread
LEFT OUTER JOIN Msg ON Msg.threadid = Thread.id
GROUP BY Thread.id DESC
ORDER BY Msg.date DESC

I could probably do an unsorted query selecting the Thread.id and max(Msg.date), dumping it into an array, sorting the array, and so on, but I would like to know how to do it in MySQL for present and future use.

Thanks!
Jim

 

[TonyGroves]

How about:
[tt]
SELECT thread.id, MAX(msg.date) lastdate
FROM
thread
LEFT JOIN msg ON msg.threadid=thread.id
GROUP BY id
ORDER BY lastdate DESC
[/tt]

 

[JGH]

Thanks, Tony!

I tried in the ORDER BY clause using "MAX(msg.date) DESC" and it always yelled at me. But your way worked perfectly!

Thanks again - Jim

 

[JGH]

Ah. A new complication. This isn't a result of Tony's fine code above, but some strange problem relating to GROUP BY.

Ordering the below SELECT with "ORDER BY date DESC" and "date ASC", the result is the same. It's a single row that is neither the oldest nor the most recent. Without "GROUP BY threadid", the 7 rows where threadid=123 are returned, and sorted correctly.

SELECT * 
FROM Message
WHERE threadid=123
GROUP BY threadid
ORDER BY date DESC

I don't know if this matters, but I did notice that for some reason, the ID value of that single incorrect row is lower than the ID of the row with earliest date. Even though in theory with auto increment, each subsequently added row would have a higher ID value.

Other stuff maybe you'll ask - the ID is my primary key, the table type is MyISAM, my real query is more complicated than that one, and my favorite transformer is Devastator.

Any thoughts?

Thanks - Jim

 

[r937]

if you have WHERE threadid=123 then you don't need GROUP BY threadid

the reason you're getting unpredictable results is because you were grouping with "hidden" fields -- you have additional columns in the SELECT (i.e. all of them, which by the way is considered poor practice) that aren't mentioned in the GROUP BY

see 12.10.3 GROUP BY with Hidden Fields for an explanation

r937.com | rudy.ca

 

[JGH]

Thanks Rudy,

The query was simplified for the sake of not getting distracted with the rest of my query, as I tried to identify where exactly things were going wrong. But I guess I should show all my cards...

What I wanted to do was get all the rows with the earliest date per threadid and userid. i.e. get the first message of each thread that user X started.

SELECT DISTINCT threadid, subject, MIN(date) as firstdate, userid
FROM Message
GROUP BY threadid DESC HAVING firstdate AND userid=$id
ORDER BY firstdate DESC

How might I do what I am really trying to achieve?

Thanks - Jim

 

[r937]

What I wanted to do was get all the rows with the earliest date per threadid and userid. i.e. get the first message of each thread that user X started.

select threadid
     , subject
     , `date` as firstdate
     , userid
  from Message as T
 where userid = $id
   and `date`
     = ( select min(`date`)
           from Message
          where userid = $id
            and threadid = T.threadid )

r937.com | rudy.ca

 

[JGH]

Thanks again, Rudy. You're on fire!

I just have to wait for my server admin folks to upgrade my MySQL above 3.23 so that I can use subqueries. In the meantime, I could relent and just do two separate queries.

Thanks - Jim

 

[r937]

pre(*)-subquery version:

select T1.threadid
     , T1.subject
     , T1.`date` as firstdate
     , T1.userid
  from Message as T1
inner
  join Message as T2
    on T2.userid = T1.userid
   and T2.threadid = T1.threadid 
 where T1.userid = $id
group
    by T1.threadid
     , T1.subject
     , T1.`date` 
     , T1.userid
having T1.`date`
     = min(T2.`date`)

pre as in prehistoric, eh ;-)

r937.com | rudy.ca