Group by

Alrua

I have this query:

SELECT * FROM forum_posts WHERE poster_id <> '2' AND forum_id = '1' GROUP BY topic_id ORDER BY post_time DESC

This gives me one record for each topic_id like it should. My problem is that i want this one record to be the newest entry (post_time should be the highest value for that group).

How do I do this? Is there anyway way I can modify the query to accomplish this?

-Alrua

tomhath

It doesn't make sense to GROUP BY something unless you are using a row aggregate function (aggregate is a GROUP of rows). And you should list every non-aggregated field that you are selecting in the GROUP (SELECT * but only GROUP BY one field is illogical, how can it select every column but only GROUP by one of them?).

Anyway, try this, I'm guessing it's what you want. It returns each topic_id, and the maximum post time within each group of rows with the same topic_id:

SELECT topic_id, max(post_time)

FROM forum_posts
WHERE poster_id <> '2' AND forum_id = '1'
GROUP BY topic_id

Alrua

your solution seems to work just fine 🙂
thanks alot 🙂

greywire

Seems like I am trying to do roughly the same thing but I cannot get it to work.

I have a database table something like this:

Lead_id, user_id, date, status.

And of course a user table I will join with this one on the user_id. I want to group on the lead_id, and I want only the most recent date for that lead_id. I want to end up with something like this:

username, lead_id.
username, lead_id
etc.

I can use max(date) which gives me the right date but the username wont be the right one for the record with that date.

Does that make any sense?

I thought the HAVING clause would work but it doesnt seem to.

tomhath

This might work, I didn't test it. It uses a weird looking self LEFT JOIN to do what would normally be done with a subselect or EXISTS.

select Lead_id, user_id, date, status
from theTable a
LEFT JOIN theTable b
ON a.Lead_id = b.Lead_id
 and a.date < b.date
where b.Lead_id is null

greywire

No idea how that is supposed to work.

The only thing at this point I can think of is doing two levels of SQL query (which will result in many more than 2 queries):

I would first do a query using a GROUP BY on the lead_id. Then I'd use PHP code to go through those results and query for each lead_id ORDER BY date LIMIT 1 to get the one record I want.

Anoying.

I can get SO close with the group by, I just cant get it to choose a particular record in that group set (it seems to want to select something right in the middle of the set).

This sucks, because in one place it means the difference between one query and 10's of thousands...

tomhath

No idea how that is supposed to work.

Then try to figure it out, otherwise why ask for help?

Normally you could do it this way (and you can if you have MySQL 4.x). But if you are on MySQL3.xx you have to do the equivalent of the "not exists" with a left join. It actually works.

select Lead_id, user_id, date, status
from theTable a
where not exists ( 
  select 1 from theTable b
  where a.Lead_id = b.Lead_id 
    and a.date < b.date)

greywire

OK, I got your example to work. I dont quite understand it, but it works. This is the query I have right now:

SELECT xuser.name, a. * FROM lead_status a LEFT JOIN lead_status b ON a.lead_id = b.lead_id AND a.adate < b.adate INNER JOIN xuser ON a.requestor_id = xuser.user_id WHERE b.lead_id IS NULL

Now, there's another thing I need. I need to check for a field: 'prev_status' = 1. If there's more than one, then it takes the one with the most recent date. So the above query as it is gives the most recent date; where do I put in the additional test to get what I want?

Thanks for the help. I'm getting closer!

tomhath

Assuming you want the latest date with a prev_status of 1, even if there's a later row with prev_status not 1

Without a db to try it out, I'd guess that you need it in both places, you want to limit the join of a to b on rows with the same status (which will mean that one row of each status will come from "a" with no matching "b" row, because there is no row with that status that has a greater date, so the left join will substitute NULLs for every column in "b"). Then you want to select only that row (WHERE "b" is null to eliminate all the rows from "a" that found rows from "b" with later dates) with prev_status=1.

ON a.lead_id = b.lead_id AND a.adate < b.adate and a.prev_status=b.prev_status
...
WHERE b.lead_id IS NULL and a.prev_status = 1