GROUP BY on most recently inserted rows

goa103

Hello,

I have a Visits table with IPs and Dates. Each time a user visits a page, its IP is inserted into the Visits table. I would like to get the most recently inserted IP after a GROUP BY Ip.

As you can't ORDER BY then GROUP BY, I don't know how to do that. "SELECT * FROM Visits ORDER BY InsertDate GROUP BY Ip" doesn't work.

The solution I found was to create 2 table, one that stores the visits and an other, LastVisits, that store the most visits:

LastVisits = Ip, LastVisitDate

So first time a user visits the page, its IP is inserted in both tables. But after that, each time it visits the page, its IP is only inserted into Visits, and the LastVisitDate field of LastVisits is just updated.

Is there anyway to do that with MySQL ? Without using 2 tables ? Maybe somekind of "GROUP BY LAST"...

Thanks,
JM

Crix_Talra

here is a table join as that is what it sounds like you are after.
modify the code as needed

 $sql = "SELECT *.LastVisits, *.visits FROM LastVisits, visits ORDER BY InsertDate.LastVisits GROUP BY Ip.visits";

goa103

Hi,

You misunderstood me. I said "I have the solution". I am looking for a solution with 1 table, no LastVisits table. I want to use a simple MySQL query to handle the whole "last visits" issue.

Like I said before, you can't ORDER BY then GROUP BY, that's my whole problem.

Thanks anyway,
JM

goa103

SQL query I use:
SELECT Ip, MAX (InsertDate) FROM Visits GROUP BY Ip

I forgot I could use a function to select columns after a group by. Things you can't do with an ORDER BY.

I hope it will help someone else,
JM