Search code examples
phpmysqlsqlidiorm

MySQL Query Distinct in Paris PHP

I have a requirement to use Paris/idiorm PHP for building my queries. I tried many possibilities and yet couldn't find a solution. I am new to PHP and SQL and don't know much about ORM. I just thought someone would help me out in building the query to get distinct names of the last updated timestamp.

-----------------------------------------
id  |   name       |    timestamp
-----------------------------------------
1   |   testuser   |  2012-11-19 17:02:48
2   |   testuser   |  2012-11-28 11:15:46
3   |   testuser   |  2012-11-19 11:02:49
4   |   testadmin  |  2012-11-28 10:50:05
5   |   testadmin  |  2012-11-28 11:15:46
-----------------------------------------

I did the following query and was able to get the names and their first timestamp but couldn't get the latest timestamp

    $output = ORM::for_table('sample')
                ->raw_query('SELECT id,name,timestamp FROM sample GROUP BY name',array())
                ->find_many();

hope there's some solution to this.

Solution

  • get the latest timestamp for each name in a subquery then join it back against itself. try,

    SELECT a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT name, MAX(timestamp) maxStamp
            FROM tableName
            GROUP BY name
        ) b ON a.name = b.name AND
                a.timestamp = b.maxStamp