mysql Query: Select Lastest Where Another Row With N doesn't exist

Question

Hi, sorry about the title, I'm not sure how to even describe this, which makes it even harder to search for a solution.

I have a table which has many answers:

CREATE TABLE `answers` (
  `a_id` int(11) NOT NULL auto_increment,
  `p_id` int(11) NOT NULL default '0',
  `q_id` int(11) NOT NULL default '0',
  `user_id` int(11) NOT NULL default '0',
  `correct` int(1) NOT NULL default '0',
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`a_id`)
) ;

I need to select a q_id from a specific user_id and p_id where correct = 0, but only where a more recent row from the same user_id and p_id's correct field is not 1.

I can group by id, but am unsure how to eliminate groups where the top correct != 0

Thanks in advance. I've found many helpful answers here and am looking forward to contributing where I can.

EDIT: Currently the query as stands, but takes 6 secs to execute:

From both your answers I have query that works, but takes 6 secs to execute!

SELECT a.q_id FROM answers a    
JOIN (SELECT b.q_id, MAX(b.a_id) as a_id, b.correct
        FROM answers b  
       WHERE b.correct = 1 
         AND b.user_id = 1 
         AND b.p_id = 22
    GROUP BY b.q_id) c ON a.q_id = c.q_id 
                      AND a.a_id > c.a_id
WHERE a.correct = 0 
  AND a.user_id = 1 
  AND a.p_id = 22  
LIMIT 1

With no JOIN they take .26 secs. and .45 secs to execute How can I make it more efficient?

Is there another way to select the most recent correct = 0 row where a prev correct = 1 row does not exist?

Thanks for all your help!

Answer 1

use the timestamp infomation to get the latest. You can order the records according to the time.

Answer 2

Since you have an auto increment field a_id, you can use it to find the latest entry. Timestamp calculations are slower than primary key sorting.

SELECT a.a_id, a.q_id, a.user_id, a.p_id, a.correct, c.correct
    FROM answers a 
    JOIN (SELECT b.p_id, b.user_id, b.correct
              FROM answers b 
              ORDER BY a_id DESC
              LIMIT 1) c
        ON (a.p_id = c.p_id
        AND a.user_id = c.user_id)
    WHERE c.correct != '1' 
    AND a.correct = '0'
    AND a.user_id = '1234'
    AND a.p_id = '5678'
    ORDER BY a.a_id DESC
    LIMIT 1;

If you don't want to limit the number of results, remove the last two lines of code.

Answer 3

This is the method that I almost always use in these situations. It's ANSI compliant and in MS SQL Server the performance is typically much better than using subqueries. I can't speak to performance in MySQL though:

SELECT
     A1.q_id
FROM
     Answers A1
LEFT OUTER JOIN Answers A2 ON
     A2.p_id = A1.p_id AND
     A2.user_id = A1.user_id AND
     A2.timestamp > A1.timestamp AND
     A2.correct = 1
WHERE
     A1.p_id = 22 AND
     A1.user_id = 1 AND
     A1.correct = 0
     A2.a_id IS NULL   -- This can only happen if no rows were found matching the JOIN criteria

BTW, if you want "latest" then you should be looking at the timestamp and not the a_id. Although it might almost always be the case (when inserting at least) that the numbers are sequential based on time inserted, it's usually not guaranteed. This is absolutely the case in MS SQL and I'm pretty sure that it's true in MySQL as well due to transactions and potential rollbacks affecting the IDs.

Answer 4

This query seems to be working best. It's my edited answer, updated with ordering the a.a_id and limiting the return to one. It returns the correct result at .15 seconds.

SELECT a.q_id FROM user_q_answers a
JOIN (SELECT b.user_id, b.popling_id, b.q_id, MAX(b.a_id) as a_id FROM user_q_answers b
WHERE b.correct = 1 GROUP BY b.q_id, b.user_id) c ON a.q_id = c.q_id AND a.user_id = c.user_id AND a.a_id > c.a_id WHERE a.correct = 0 AND a.user_id = 101 AND a.popling_id = 170
ORDER BY a.a_id DESC LIMIT 1

Thanks very much to OMG Ponies and Nirmal for helping me through this. Sorry if answering my own question is bad form here, but I comprised my query based on what I learned from you both.

Thanks again!!!