Displaying the latest date on a group by query instead of the 1st

[odsska]

Hey guys. I have a select statement that retrieves customer activity. What I want the result to display is each active company in a specified timeframe.

Currently when I do a group by on the result (so that it only shows each company once) the date value has the 1st login instead of the most recent...

Here is the statement so far.

SELECT account.company, date_format(session.last_activity, '%c/%e/%Y') as last_active
FROM account, session, user
WHERE session.user_id !=0
AND session.user_id = user.user_id
AND user.account_id = account.account_id
AND DATE_SUB( CURDATE() , INTERVAL 30 DAY) <= session.last_activity
GROUP BY account.company
ORDER BY session.last_activity DESC

Currently outputs:

Account Login
-----------------
Angie's List 6/29/2006
David Stollmack 6/28/2006
Bill Corbin 6/28/2006
Gatesville Country Store 6/27/2006

Each of these accounts have logged in the last few days... so I really need to show that.

Any help would be greatly appreciated!!

 

[odsska]

Completely forgot to use max() in front of the date! Now its the most recent login but it is sorting by company (i assume thats from the group by) and not by date...

 

[TonyGroves]

If you want to order by MAX(session.last_activity), you would need to include that in your result set.

 

[r937]

if you want to sort by some column in a GROUP BY query, then that column must be in the SELECT list

your ORDER BY was for session.last_activity, but that column isn't in the SELECT list -- instead, a string based on it is

try this --

[COLOR=red]select company
     , [b]last_act[/b]
     , date_format([b]last_act[/b]
            , '%c/%e/%Y') as last_active
  from ([/color]
SELECT account.company
     , max(session.last_activity) as [b]last_act[/b]
  FROM session
inner
  join user
    on user.user_id = session.user_id 
inner
  join account
    on account.account_id = user.account_id 
 WHERE session.user_id <> 0
   and session.last_activity
        >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP 
    BY account.company
       [COLOR=red]) as data
ORDER
    BY [b]last_act[/b] DESC[/color]

r937.com | rudy.ca