Ordering with GROUP BY

[StupidRalph]

SELECT `member_id`,`time`
FROM `mem_access_log`
WHERE `time` >= DATE_SUB(NOW(), INTERVAL 30 MINUTE)
GROUP BY `member_id`;

How would I order this with the most recent time being first? I know you can use the ASC and DESC on the GROUP BY clause but I can't get it to work in the manner in which I seek. I've also tried using ORDER BY `time` DESC to no avail. I'm using MySQL 4.0.25 (I need to change host b/c they refuse to upgrade :rolleyes: )

//Here are the results if I were NOT to use the GROUP BY.
+-----------+---------------------+
| member_id | time |
+-----------+---------------------+
| 6 | 2007-08-09 23:42:17 | <--- I do not want to use this time.
| 6 | 2007-08-09 23:43:48 |
| 6 | 2007-08-09 23:43:51 | <--- I want to use this time
| 52 | 2007-08-10 03:11:11 |
+-----------+---------------------+
4 rows in set (0.00 sec)


//With the GROUP BY statement
+-----------+---------------------+
| member_id | time |
+-----------+---------------------+
| 6 | 2007-08-09 23:42:17 | <---it uses the earliest time in the group
| 52 | 2007-08-10 03:11:11 |
+-----------+---------------------+
2 rows in set (0.00 sec)

[guelphdad]

you actually have three times there for member_id 6 and need to dispose of two of them. This one should be discarded too right? 2007-08-09 23:43:48

note your question has nothing to do with the order by clause, but is a question about latest or eariest record per group.

Read this (http://www.guelphdad.wefixtech.co.uk/sqlhelp/latestfromgroup.shtml) and modify as necessary.

[StupidRalph]

That is correct, I only pointed out what was been using and what I wanted to be used. Sorry for the confusion. I'll check out the article.

[StupidRalph]

I have MySQL 4.0.25. I supose I can rewrite that subquery using a LEFT JOIN? I'll give it a shot.

[guelphdad]

you don't need an OUTER join because you only want matching rows.
use a self join on the table with a having condition

i.e.
HAVING
a.timestampcolumn=max(b.timestampcolumn)