Difficulty with SQL query

Question

I am trying to write a query for an online coding event website, but it is hard for me to handle this.

I have a view in a table:

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

The user could make several submissions for a given pid.

I want to know: who was the last user to present my solution for each problem?

At the moment, I don't care if the decision was right or wrong.

The request that I would work was

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

but it does not work the way I want it. This query finds the maximum ts in order, but the uid associated with it is invalid.

Can someone teach me what is wrong with my request? and what is the correct way to write a query for my purpose?

thank

Note. I am using mysql.

Edit: I could do this by repeating all the pids and writing

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

. , , . , .

Answer 1

, , :

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;

, , , SELECT.

Answer 2

pid = 1234:

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

, pid ( pid " subts pid" )

---

: pid, uid { subts }; pid.

, select , pid uid, .