Difficulty with an SQL query.

Question

Hi,

I'm trying to write a query for an online coding event website, but I'm finding it particularly difficult to deal with.

I have a table submissions:

submissions => subid | uid | pid | subts | status

uid = userid
pid = problem id
subts = timestamp when the submission was submitted
status = whether the answer is right or not

A user might have made multiple submissions for a given pid.

I want to find out: who was the latest user to submit his solution for every problem?

For now, I don't care if the solution was right or wrong.

The query I though would work was

select pid, uid, max(subts) from submissions group by pid;

but its not working the way I want it to. This query finds the maximum ts alright, but the uid associated with it not correct.

Can anyone teach me what's wrong with my query? and what is the right way to write a query for my purpose?

Thanks

Note: I'm using mysql.

Edit: I could do this by iterating over all the pids, and writing

select pid, uid, max(subts) from submissions where pid=$pid order by subts desc limit 1;

But I don't really want to do this. I want to know if a single query can accomplish what I want. If yes, I want to know how.

Answer 1

For pid = 1234:

SELECT pid, uid, subts FROM submissions WHERE pid = 1234 ORDER BY subts DESC LIMIT 1;

In other words, find all rows with this pid, and get the largest subts from those rows (you can have different pids with different "largest subts for this pid")

---

Your original query says: for every row, give me pid, uid, and {the largest subts in the whole table}; only return the first row with the given pid.

So your select takes the first row, gets pid and uid from it, then looks for largest subts in all rows.

Answer 2

This is a bit more complicated than you would think but the below query should work:

SELECT s.uid, s.pid, s.subts
FROM submissions s,
   (SELECT max(subts) as maxdate, pid
     FROM submissions
     GROUP BY pid) maxresults
WHERE s.pid = maxresults.pid
AND s.subts = maxresults.maxdate;

Whether or not you count this as a single query or not is up to you, but I believe it cannot be done by a single SELECT statement.