Wilcard search against Max value

[kritro]

I have a query

SELECT radcheck.id, radcheck.UserName, radcheck.epost, radcheck.fylke, MAX( radacct.AcctStopTime ) AS AcctStopTime
FROM radcheck
LEFT JOIN radacct ON radcheck.UserName LIKE radacct.UserName
WHERE AcctStopTime LIKE '%2009-03-19%'
GROUP BY radcheck.id

I'm looking for a way to use wilcards, > <, NOT LIKE etc on only the highest date value AcctStopTime in the radacct table. The above query without the WHERE
clause gives me only the highest value, but I cant find a way to check against this highest value.

My user table radcheck holds the users and the radacct table holds user sessions. There are many sessions on each user, but here I'm only interested in the last session.

If this was the tables

radcheck
+--------+
|UserName|
+--------+
| user1  |
| user2  |
| user3  |
+--------+

radacct (table over sessions)
+----------+---------+-----------+
| UserName | session | date      |
+----------+---------+-----------+
| user1    |    1    | 2009-01   |
| user1    |    2    | 2009-04   |
| user2    |    1    | 2008-11   |
| user2    |    2    | 2009-03   |
| user3    |    1    | 2008-11   |
+----------+---------+-----------+

If I wanted to find all users with their last session later then 2009-02. This should produce a list of user1 and user2 since they have sessions in 2009-03 and 04.

My fist idea whould be to use

SELECT radcheck.UserName, MAX( radacct.date ) AS date
FROM radcheck
LEFT JOIN radacct ON radcheck.UserName LIKE radacct.UserName
WHERE MAX(radacct.date) > '2009-02'
GROUP BY radcheck.UserName

This gives an error messsage as of to wrong use of Group By.

The other query first in the post dont gives error but that is checking against all sessions and not just the last on every user since WHERE clause check the radcheck
table in general and not the ones with the highest date.

Anyone knows how to solve this?

kritro

[darkagn]

Change GROUP BY to ORDER BY in the second query. I think this will give you the result you are looking for.

[kritro]

I could'nt use the ORDER BY. I think its because when aggregated functions like MAX() is mixed with regular you have to use GROUP BY.

I got it working with the HAVING clause.

SELECT radcheck.id, radcheck.UserName, radcheck.epost,    radcheck.fylke, MAX( radacct.AcctStopTime ) AS AcctStopTime
   FROM radcheck
   LEFT JOIN radacct ON radcheck.UserName LIKE radacct.UserName
   GROUP BY radcheck.id
   HAVING AcctStopTime LIKE '%2009-03-19%'