MySQL ORDER BY with GROUP BY

I want to select blocket out clients stored in MySQL table

[forsberg]

Hello,

I have a problem that I cannot solve myself. I hope you can help me to accomplish what I am after.

Let us start with my table layout and I will explain later on:

Table name: cms_cli_loginattempt

#
# Table layout:
#
loginattempt_id (int11 auto_increment primary key) // Stores unique auto increment ID no.
loginattempt_ipaddress (varchar 15) // Stores IP-address
loginattempt_time (varchar 15) // Stores time of login attempt (unix timestamp)

#
# Table example data:
#
loginattempt_id ----- loginattempt_ipaddress ----- loginattempt_time
============================================================
56 ----- 127.0.0.1 ----- 1258711034
57 ----- 127.0.0.1 ----- 1258711042
58 ----- 127.0.0.1 ----- 1258711049
59 ----- 127.0.0.1 ----- 1258711060
60 ----- 192.168.0.1 ----- 1258711060
61 ----- 196.251.9.047 ----- 1258711002
============================================================

Now I want to SELECT any rows where loginattempt_time is older than X minutes.
It should also SORT BY last row added (by sorting on the loginattempt_time column).
One other thing is also that I only want to return one IP-address once.

So by selecting from my example above, I want to return the following:

loginattempt_id ----- loginattempt_ipaddress ----- loginattempt_time
============================================================
59 ----- 127.0.0.1 ----- 1258711060
60 ----- 192.168.0.1 ----- 1258711060
61 ----- 196.251.9.047 ----- 1258711002
============================================================

Can anyone help me with a query to accomplish this? I have already tried:

"SELECT * FROM cms_cli_loginattempt WHERE loginattempt_time < UNIX_TIMESTAMP( 'YYYY-MM-DD HH:MM' )
GROUP BY loginattempt_ipaddress ORDER BY loginattempt_time DESC" (where YYYY-MM-DD HH:MM = 60 minutes ago)

This gives me unique IP-addresses only by using the GROUP BY, but it does not return
the last row inserted but only sorts the result by loginattempt_time, so it does not do what I want.

I also found this post: [webmasterworld.com...]
It almost does what I want, but it does not select only rows that is older than 60 minutes, but it
sorts by the auto increment ID number, and that is not what I am looking for.

I hope someone can help me out here!

Thank you in advance!

[forsberg]

I think I solved it by using this query:

"SELECT cms_cli_loginattempt . * FROM cms_cli_loginattempt
INNER JOIN (SELECT MAX( loginattempt_id ) AS loginattempt_id
FROM cms_cli_loginattempt WHERE loginattempt_time < UNIX_TIMESTAMP('2009-11-20 12:58')
GROUP BY loginattempt_ipaddress) loginattempt_ids
ON cms_cli_loginattempt.loginattempt_id = loginattempt_ids.loginattempt_id"

What do you think? Can this be optimized or made easier or is this the best way to go?