[SOLVED] Querying the MAX row

[kevinfwb]

I have a database that we use to log communication with our stores.  I have a summary page that (I want) to show the most recent communication for each store number (storeNum).  I'm pretty sure that I need to use the aggregate function, MAX, on the date field and/or possibly a subquery but I just can't seem to figure this one out.

 

Here is the basic query that returns all communications for all dates for all stores.  I want it to just return the most recent communication for each store.

 

SELECT communications.storeNum as storeNum , communications.method as method, communications.date as date,

communications.subject as subject, communications.details as details, Stores.franchisee as franchisee, Stores.rfc as rfc

FROM communications INNER JOIN Stores ON communications.storeNum = Stores.storeNum

ORDER BY communications.date DESC

 

Any help is greatly appreciated

 

-Kevin

[fenway]

You're right, a second query would be best... use MAX(datetimeField) on the comm table, and get back the date/time and storeNum... then join this derived table to the stores table.

[kevinfwb]

I don't think that I've ever done that before, would this still be a single query with a subquery or 2 separate queries all together?  I'd like it to be a single query since I'm using it to populate an HTML table at the same time.

[fenway]

I don't think that I've ever done that before, would this still be a single query with a subquery or 2 separate queries all together?  I'd like it to be a single query since I'm using it to populate an HTML table at the same time.

As far the PHP side is concerned it doesn't matter... it's 2 "queries", but one statement... something like this (untested) -- you may need to backtick the date column (reserved keyword!):

 

SELECT c.storeNum, c.method c.date, c.subject as subject, c.details s.franchisee s.rfc
FROM communications AS c
INNER JOIN stores AS s ON c.storeNum = s.storeNum
INNER JOIN 
( SELECT storeNum, MAX(date) AS maxDate FROM communications GROUP BY storeNum ) AS rc ON rc.storeNum = c.storeNum and rc.maxDate = c.date
ORDER BY c.date DESC

[kevinfwb]

This worked perfectly - thanks for your help!

 

-Kevin