ORDER and GROUP together

[gr8gatzb]

I would like to use both of these together, but I can't control the order. For example, I have a list of IPs and dates. I want to group by and collapse the IPs, but then have the most recent dates returned.

 

SELECT * FROM test GROUP BY ip ORDER BY date DESC

 

It collapses on the IP but doesn't return the most recent date. How do I fix this?

[pocobueno1388]

What format and type is your date field?

[aschk]

Because MySQL is returning a random date basically.

It's compacting all the data into IP groupings, but for each row that exists when it groups 1 ip (192.168.1.128 for example) together which date value does it pick?

 

This looks rather similar to a post I made recently...

 

SELECT t.ip, x.max_date
FROM test
JOIN 
  (SELECT ip, MAX(date) as 'max_date'
   FROM test
   GROUP BY ip
) x ON x.ip = t.ip AND x.max_date = t.date
ORDER BY max_date DESC;