How can I write a SQL query which ensures two columns are independently unique?

Question

I have a MySQL table which looks like the following:

user_id  resource_id   last_used
--------------------------------
      1            1  2010-09-01
      1            2  2010-09-02
      1            3  2010-09-04
      2            3  2010-09-08
      2            2  2010-09-03
      3            1  2010-09-01
      3            1  2010-09-05

I need to write a query which tells me which resource was used last by which user, with each user and each resource only listed at most once (it's okay if some users and some resources aren't listed). In the above case, I'd like the following result set:

user_id  resource_id   last_used
--------------------------------
      2            3  2010-09-08
      3            1  2010-09-05

Note that each user id appears at most once, each resource id appears at most once, and preference is given to the most recent last_used date. It's okay for my purposes that user 1 and resource 2 don't appear at all (although it would be also be fine if 1 2 2010-09-02 appeared in the results). This query needs to run on a potentially large database (500,000+ rows). Is there an easy, reasonably fast query to do this in SQL?

Answer 1

Try like this for start

SELECT 
  t.user_id, t.resource_id, t.last_used 
FROM 
  tableName AS t 
CROSS JOIN (
  SELECT resource_id, MAX(last_used) AS last_used FROM tableName GROUP BY resource_id
) AS sq 
USING (resource_id, last_used)

You'll probably need a composite index on (resource_id, last_used) columns to make it quicker.

Answer 2

Use:

SELECT x.*
  FROM YOUR_TABLE x
  JOIN (SELECT t.resource_id,
               MAX(t.last_used) AS max_last
          FROM YOUR_TABLE t
      GROUP BY t.resource_id) y ON y.resource_id = x.resource_id
                               AND y.max_last = x.last_used