Select distinct values from two columns

Question

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 - itemId and 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!

Answer 1

Use:

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.

Answer 2

select t1.itemid, t1.direction, t1.uid, t1.created
from  (select t2.itemid, t2.direction, t2.created as maxdate 
       from tbl t2 
       group by itemid, direction) x
inner join tbl t1
  on  t1.itemid = x.itemid
  and t1.direction = x.direction
  and t1.created = x.maxdate