Question 201956 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to select recent 2 records from multiple records for multiple study ids?

Below is my table script.
I have multiple entries for the same study id with different api_cron_job_id, created_at and status can be Complete OR In Progress.

basically, we are inserting records based on study id with different values for all columns and timestamps in created at the column for e.g. here is the insert script

INSERT INTO `analytics_cron_refresh_time` (`id`, `study_id`, `client_id`, `last_completion_date`, `status`, `created_at`, `modifed_at`, `api_cron_job_id`)
VALUES ('2798d9b0-ef59-496e-ba21-f2d7bdc9518d', 'c5d52f86-c0e3-46e3-a164-ac321329d78e', '654111', '2023-07-06 13:12:55.598', 'Complete', '2023-07-06 13:12:50.000', '2023-07-06 13:12:55.598', 147);

Now I want to select recent 2 entries for each study_id

Can you please help me, and please someone format this question, its my first question so all folks understand it.

CREATE TABLE `analytics_cron_refresh_time` (
   `id` varchar(100) NOT NULL,
   `study_id` varchar(100) DEFAULT NULL,
   `client_id` varchar(45) DEFAULT NULL,
   `last_completion_date` datetime(3) DEFAULT NULL,
   `status` varchar(45) DEFAULT NULL,
   `created_at` datetime(3) DEFAULT NULL,
   `modifed_at` datetime(3) DEFAULT NULL,
   `api_cron_job_id` int(11) NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`id`),
   UNIQUE KEY `api_cron_job_id_UNIQUE` (`api_cron_job_id`),
   KEY `idx_study_id` (`study_id`,`api_cron_job_id`),
   KEY `idx_study_id_status` (`status`,`study_id`,`api_cron_job_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=latin1

*I tried below script *

SELECT t1.*
FROM analytics_cron_refresh_time t1
LEFT JOIN analytics_cron_refresh_time t2 
    ON t1.study_id = t2.study_id AND t1.created_at < t2.created_at AND t1.status
WHERE t1.status = 'Complete'
GROUP BY t1.study_id
HAVING COUNT(*) > 2
ORDER BY t1.created_at DESC;

but getting incorrect data.

Tags:

2

Answers


  1. 0

    try this

    SELECT *
   FROM
     (SELECT *,
             @study_rank := IF(@current_study = study_id, @study_rank + 1, 1) AS study_rank,
             @current_study := study_id
       FROM analytics_cron_refresh_time
       WHERE status = 'Complete'
       ORDER BY study_id, created_at DESC
     ) ranked
   WHERE study_rank <= 2;

    you can find more about how session variables work here also notice that select would have 2 more columns if you wanna get rid of them try selecting specific columns in outer select instead of *

    Login or Signup to reply.
  2. 0

    This should help you in finding the latest two records of each group by study_id

     SELECT study_id, last_completion_date
    FROM (
        SELECT study_id,last_completion_date,
ROW_NUMBER() OVER (PARTITION BY study_id ORDER BY last_completion_date DESC) AS row_number
        FROM analytics_cron_refresh_time WHERE status = 'Complete') t
    WHERE t.row_number = 1 OR t.row_number = 2
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search