MySQL Select distinct values across multiple tables..

Question

I have two tables:

table_1
uid | xid | name

table_2
uid | elements | time | pkey

I want to select multiple rows of xid, elements and time where time is the 10 smallest values and uid (or xid) is distinct.

uid, xid in in table_1 is unique and the relationship between uid in table_1 and table_2 is one to many..

I tried something like this but its not really working too well:

SELECT table_1.xid, MIN(table_2.time), table_2.elements
FROM table_1, table_2
WHERE table_1.uid= table_2.uid
GROUP BY table_2.uid
LIMIT 10

Lets take some data to play around with:

table_1
uid | xid | name
1 | 435 | John
2 | 596 | Jane


table_2
uid | elements | time | pkey
1 | 1 | 567 | 0
2 | 2 | 335 | 1
1 | 1 | 435 | 2
1 | 2 | 456 | 3
2 | 1 | 337 | 4
1 | 1 | 428 | 5

How would I select the top 2 distinct results for each UID? In this case:

fid| elements | time
596| 2 | 335
435| 1 | 428

Thanks!!!

In case people don't understand why lexu's solution does not work - it does not bind to primary key on table 2

If I change above data to :

table_2
uid | elements | time | pkey
1 | 1 | 567 | 0
2 | 2 | 335 | 1
1 | 1 | 435 | 2
1 | 2 | 456 | 3
2 | 1 | 337 | 4
1 | 2 | 428 | 5

Keep table_1 the same then the result should be:

fid| elements | time
596| 2 | 335
435| 2 | 428

but with @lexu's solution result is:

fid| elements | time
596| 2 | 335
435| 1 | 428

Nonetheless, thanks everyone for the help and especially @eagle!

Answer 1

Here's my solution. I thought that rather than just give the working query, I will go step-by-step through my thought process and the queries I tried in each step:

First, let's select the 10 smallest times for distinct uid:

select uid, min(time)
from table_2
group by uid
order by 2
limit 10

This gives us:

uid | time
2 | 335
1 | 428

That was easy... unfortunately, this doesn't allow us to grab the primary key, which will be a problem if the following row is added:

table_2
uid | elements | time | pkey
1 | 2 | 428 | 6

In future queries, when we try to join on the time and uid, we will get two records rather than 1. So we need a better query that returns a distinct value (e.g. pkey) rather than the time, which I am assuming can be non-distinct...

Note: This would be much simpler if MySQL had the FIRST() or LAST() aggregate functions. Unfortunately, it doesn't so we must settle for a sub-query, order-by, limit combo.

select
  t2.uid,
  (select pkey from table_2 t3 where t2.uid = t3.uid order by t3.time asc limit 1) as minpkey
from
  (select uid, min(time) from table_2 group by uid order by 2 limit 10) t2

This will now return results we can work with:

uid | minpkey
1 | 5
2 | 1

Notice that the 5 was chosen randomly and the 6 could have just as easily been chosen; it all depends on how mysql decides to choose it. But for us, it doesn't matter.

Next we want to show more data (namely the xid and elements fields):

select t1.xid as fid, t5.elements, t5.time
from 
(select
  t2.uid,
  (select pkey from table_2 t3 where t2.uid = t3.uid order by t3.time asc limit 1) as minpkey
from
  (select uid, min(time) from table_2 group by uid order by 2 limit 10) t2
) t4
inner join table_1 t1 on (t4.uid = t1.uid)
inner join table_2 t5 on (t4.minpkey = t5.pkey)

And viola, it should return the exact same data you provided in your example! It might not be very efficient, but it should work!