0

I have a table activities:

id  last_active login   ip
1   1000        user1   192.168.10.10
2   2000        user2   192.168.10.20
3   3000        user3   192.168.10.30
4   4000        user1   192.168.10.10
5   5000        user2   192.168.10.20
6   6000        user2   192.168.10.20
7   7000        user1   192.168.10.10

I have to select all ip and other data in a row, but only where last_active is the highest. So the result should like that:

3   3000    user3   192.168.10.30
6   6000    user2   192.168.10.20
7   7000    user1   192.168.10.10

I've tried it by using:

ORM::factory('Activity')->order_by('last_active','DESC')->group_by('ip')->find_all();

and the result is:

array(4) (
    "id" => string(1) "3"
    "last_active" => string(4) "3000"
    "login" => string(5) "user3"
    "ip" => string(13) "192.168.10.30"
)
array(4) (
    "id" => string(1) "2"
    "last_active" => string(4) "2000"
    "login" => string(5) "user2"
    "ip" => string(13) "192.168.10.20"
)
array(4) (
    "id" => string(1) "1"
    "last_active" => string(4) "1000"
    "login" => string(5) "user1"
    "ip" => string(13) "192.168.10.10"
)

As you can see, I have distinct ips but the last_active value is not correct. Any idea? Pure SQL statement or DB::select() answer is OK :-)

piterek
  • 47
  • 1
  • 6

2 Answers2

0

how about 

select id,  last_active, login,   ip
from activities
where (last_active, ip) in (select max(last_active), ip from activities group by ip )
Argod
  • 161
  • 5
  • Works! Thank you. Now I have to figure out how to convert this into the Kohana ORM or DB Query Builder :) – piterek Nov 04 '14 at 18:51
  • in your question you said that pure SQL was ok. I cant help you if you need it in your ORM :/ – Argod Nov 04 '14 at 19:17
0

Use DB::expr(). Something like the following should work:

ORM::factory('Activity')
    ->where(
        DB::expr(
            '`last_active`,`ip`'
        ),
        'IN',
        DB::expr(
            'select max(`last_active`), `ip` from `activities` group by `ip`'
        )
    ->find_all();
user4218398
  • 1