Jump to content

[SOLVED] Querying the MAX row


kevinfwb

Recommended Posts

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

Link to comment
https://forums.phpfreaks.com/topic/74373-solved-querying-the-max-row/
Share on other sites

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.