I have a table with the following structure:
itemId | direction | uid | created 133 0 17 1268497139 432 1 140 1268497423 133 0 17 1268498130 133 1 17 1268501451
I need to select distinct values for two columns -
direction, so the output would be like this:
itemId | direction | uid | created 432 1 140 1268497423 133 0 17 1268498130 133 1 17 1268501451
In the original table we have two rows with the
itemId - 133 and
direction - 0, but we need only one of this rows with the latest created time.
Thank you for any suggestions!
SELECT t.itemid, t.direction, t.uid, t.created FROM TABLE t JOIN (SELECT a.itemid, MAX(a.created) AS max_created FROM TABLE a GROUP BY a.itemid) b ON b.itemid = t.itemid AND b.max_created = t.created
You have to use an aggregate (IE: MAX) to get the largest created value per itemid, and join that onto an unaltered copy of the table to get the values associated with the maximum created value for each itemid.