| nicolasd92 2005-05-13, 1:35 am |
| Hi
I have a table like that:
addr_ip date
127.0.0.1 20050512140758
127.0.0.1 20050512143522
127.0.0.1 20050512143710
127.0.0.1 20050512203349
24.60.19.142 20050512203407
24.60.19.142 20050512203409
24.60.19.142 20050512203409
127.0.0.1 20050512215434
24.60.19.142 20050512215451
127.0.0.1 20050512225606
127.0.0.1 20050512225617
24.60.19.142 20050512225712
And I would like a result like this:
addr_ip date min( sup.date)
127.0.0.1 20050512140758 20050512143522
127.0.0.1 20050512143522 20050512143710
127.0.0.1 20050512143710 20050512203349
127.0.0.1 20050512203349 20050512203407
127.0.0.1 20050512215434 20050512215451 w
127.0.0.1 20050512225606 20050512225617 w
127.0.0.1 20050512225617 20050512225712 w
24.60.19.142 20050512203407 20050512203409
24.60.19.142 20050512203409 20050512215434 w
24.60.19.142 20050512215451 20050512225606 w
As you see I'm trying to write in one record the interval of 2 following positions for the same @IP. I tried this after hours of efforts but it doesn't work well:
SELECT tab.addr_ip, tab.date, min( sup.date )
FROM visit tab, visit sup
WHERE tab.date < sup.date
GROUP BY tab.date
ORDER BY tab.addr_ip, tab.date
Do you have any other solution, sources that would help me?
Nick
Thanks. |