Last ID Record Of Each Group By

[berridgeab]

Hi,

 

MySQL Version - 5.1.46

 

Ive been tearing my hair out over what I consider a relatively easy MySQL query for 3 days now to no avail. 

 

I want the last Record of each Group By. 

 

I am hoping somebody here can help, belowis the query in its current form. Below that Ive attached images of what the returned record set looks like in its current form, and a picture of what I want it to look like.

 

Please ignore the duplicate columns,  I included them when I was trying 'debug' my SQL.

 

SELECT
c.CatagoryID                        AS 'Catagory ID',
c.CatagoryTitle                     AS 'Catagory Title',
c.CatagorySubject                   AS 'Catagory Subject',
COUNT(DISTINCT mCount.MessageID)    AS 'Total Messages',
COUNT(DISTINCT pCount.PostID)       AS 'Total Posts',
Hope.*

FROM 

onecall.sv2_department_members dm, 
onecall.sv2_department_rules dr, 
onecall.sv2_message_catagory c, 
onecall.sv2_message_message mCount, 
onecall.sv2_message_post pCount

JOIN 
(
    SELECT 
    p.PostID, 
    p.MessageID AS 'Post MsgID',
    m.MessageID, 
    m.CatagoryID, 
    p.PostTime, 
    p.Post 
    FROM onecall.sv2_message_post p, onecall.sv2_message_message m 
    WHERE m.MessageID = p.MessageID
    GROUP BY p.PostID
) AS Hope

WHERE 

dm.UserID = '168' AND 
dr.DepartmentID = dm.DepartmentID AND 
c.CatagoryID = dr.CatagoryID AND 
mCount.CatagoryID = c.CatagoryID AND 
pCount.MessageID = mCount.MessageID AND
Hope.CatagoryID = c.CatagoryID
    
GROUP BY Hope.CatagoryID, Hope.PostID
ORDER BY Hope.CatagoryID, Hope.PostID ASC

 

I wish it to return the following -

 

Also the EXPLAIN statement -

 

 

[attachment deleted by admin]

[fenway]

* and GROUP BY don't mix.

[berridgeab]

Yeah, I only used the Hope.* to save me a bit of typing to otuput all fields from that table. But even when I specifically selecr the columns I need from table Hope it still gives the same result set, so thats not the problem.

 

I really need to move on with this so for now Ive opted to use -

 

SELECT
c.CatagoryID                        AS 'Catagory ID',
c.CatagoryTitle                     AS 'Catagory Title',
c.CatagorySubject                   AS 'Catagory Subject',
COUNT(DISTINCT mCount.MessageID)    AS 'Total Messages',
COUNT(DISTINCT pCount.PostID)       AS 'Total Posts',
Hope.PostID                         AS 'Hope.Post ID',
Hope.MessageID                      AS 'Hope.Message ID',
p.PostID                            AS 'p.Post ID',
p.MessageID                         AS 'p.Message ID',
Hope.PostTime,
Hope.Post

FROM 

onecall.sv2_department_members dm, 
onecall.sv2_department_rules dr, 
onecall.sv2_message_catagory c, 
onecall.sv2_message_message mCount, 
onecall.sv2_message_post pCount

JOIN 
(
    SELECT 
    p.PostID, 
    m.MessageID,
    m.CatagoryID, 
    p.PostTime, 
    p.Post 
    FROM onecall.sv2_message_post p, onecall.sv2_message_message m 
    WHERE m.MessageID = p.MessageID
    
) AS Hope 

LEFT OUTER JOIN
onecall.sv2_message_post p ON (p.PostID > Hope.PostID)

WHERE 

dm.UserID =         '168' AND 
dr.DepartmentID =   dm.DepartmentID AND 
c.CatagoryID =      dr.CatagoryID AND 
mCount.CatagoryID = c.CatagoryID AND 
pCount.MessageID =  mCount.MessageID AND
Hope.CatagoryID =   c.CatagoryID 

    
GROUP BY Hope.CatagoryID, Hope.PostID
ORDER BY Hope.CatagoryID, Hope.PostID ASC

 

From the resultset generated I can use PHP to do the rest of the filtering.

 

Feels very hacky considering all I wanted is the last row of each Hope.CatagoryID, will come back to it at a later date when I have more time to spend on it. From what I have read online, all other major databases have a very 'easy' function to do what I require, only MySQL seems to be the outsider on this one.

 

Thanks for your reply anyway.

[berridgeab]

Further to this Ive just got my desired resultset by adding a 'CatagoryID' field to my post Table. I didn't want the extra field there but it seems to be the only way to resolve my problem in one query. Never know, might aid me in the long run.

[fenway]

So, solved?

[berridgeab]

Yep, sorry, now solved thank you