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

Mysql – How do I count all DISTINCT ID groups, that exceed a certain number, and then return these IDs?

Introduction:

I want to set a limit for how many technical tests, a user can take. Once that limit is exceeded, I want to delete the remainder, which will represent the earliest submitted technical tests.
First in, first out type of system.

Detail:

MySQL 5.5

Using the following SQL:

/*
Navicat MySQL Data Transfer

Source Server         : local
Source Server Version : 50562
Source Host           : localhost:3306
Source Database       : devtester-blog

Target Server Type    : MYSQL
Target Server Version : 50562
File Encoding         : 65001

Date: 2024-01-08 20:50:01
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for tbluserquestion
-- ----------------------------
DROP TABLE IF EXISTS `tbluserquestion`;
CREATE TABLE `tbluserquestion` (
  `UserQuestion_ID` int(11) NOT NULL AUTO_INCREMENT,
  `User_ID` int(11) DEFAULT NULL,
  `Question_ID` int(11) DEFAULT NULL,
  `Technical_test_uuid` varchar(255) DEFAULT NULL,
  `Answer_text` longtext,
  `Duration` int(11) DEFAULT '10',
  `Submission_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`UserQuestion_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1663 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tbluserquestion
-- ----------------------------
INSERT INTO `tbluserquestion` VALUES ('23', '1', '36', '6f21885b-fe56-0a35-3dd181707f656b08', '', '10', '2023-10-17 16:44:28');
INSERT INTO `tbluserquestion` VALUES ('24', '1', '37', '6f21885b-fe56-0a35-3dd181707f656b08', '', '10', '2023-10-17 16:44:28');
INSERT INTO `tbluserquestion` VALUES ('25', '1', '43', '6f21885b-fe56-0a35-3dd181707f656b08', '', '10', '2023-10-17 16:44:28');
INSERT INTO `tbluserquestion` VALUES ('26', '1', '41', '6f21885b-fe56-0a35-3dd181707f656b08', 'async', '10', '2023-10-17 16:44:28');
INSERT INTO `tbluserquestion` VALUES ('27', '1', '38', '6f34ce31-987b-cb1e-a676985c54114a46', '', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('28', '1', '40', '6f34ce31-987b-cb1e-a676985c54114a46', '', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('29', '1', '41', '6f34ce31-987b-cb1e-a676985c54114a46', 'asyncXXXX', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('30', '1', '42', '6f34ce31-987b-cb1e-a676985c54114a46', '', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('31', '1', '45', '6f34ce31-987b-cb1e-a676985c54114a46', '', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('44', '1', '37', 'ff38ad50-e835-447a-49e220375e3f7769', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('45', '1', '39', 'ff38ad50-e835-447a-49e220375e3f7769', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('46', '1', '40', 'ff38ad50-e835-447a-49e220375e3f7769', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('47', '1', '42', 'ff38ad50-e835-447a-49e220375e3f7769', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('48', '1', '43', 'ff38ad50-e835-447a-49e220375e3f7769', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('188', '1', '43', 'ac9dad15-f677-8816-50eba2056f77e9ce', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('189', '1', '45', 'ac9dad15-f677-8816-50eba2056f77e9ce', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('190', '1', '47', 'ac9dad15-f677-8816-50eba2056f77e9ce', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('191', '1', '46', 'ac9dad15-f677-8816-50eba2056f77e9ce', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('192', '1', '42', 'ac9dad15-f677-8816-50eba2056f77e9ce', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('193', '1', '43', 'acafb668-aa4a-fa1e-a91ab2a0f6005d7f', '', '10', '2023-12-22 11:23:59');
INSERT INTO `tbluserquestion` VALUES ('194', '1', '42', 'acafb668-aa4a-fa1e-a91ab2a0f6005d7f', '', '10', '2023-12-22 11:23:59');
INSERT INTO `tbluserquestion` VALUES ('195', '1', '44', 'acafb668-aa4a-fa1e-a91ab2a0f6005d7f', 'nav', '10', '2023-12-22 11:23:59');
INSERT INTO `tbluserquestion` VALUES ('196', '1', '45', 'acafb668-aa4a-fa1e-a91ab2a0f6005d7f', '', '10', '2023-12-22 11:23:59');
INSERT INTO `tbluserquestion` VALUES ('197', '1', '46', 'acafb668-aa4a-fa1e-a91ab2a0f6005d7f', '', '10', '2023-12-22 11:23:59');
INSERT INTO `tbluserquestion` VALUES ('198', '1', '46', 'acbe4c54-974e-5a75-fd0a74fb06c69171', '', '10', '2023-12-22 11:25:34');
INSERT INTO `tbluserquestion` VALUES ('199', '1', '47', 'acbe4c54-974e-5a75-fd0a74fb06c69171', '', '10', '2023-12-22 11:25:34');
INSERT INTO `tbluserquestion` VALUES ('200', '1', '45', 'acbe4c54-974e-5a75-fd0a74fb06c69171', '', '10', '2023-12-22 11:25:34');
INSERT INTO `tbluserquestion` VALUES ('201', '1', '44', 'acbe4c54-974e-5a75-fd0a74fb06c69171', 'nav', '10', '2023-12-22 11:25:34');
INSERT INTO `tbluserquestion` VALUES ('202', '1', '42', 'acbe4c54-974e-5a75-fd0a74fb06c69171', '', '10', '2023-12-22 11:25:35');
INSERT INTO `tbluserquestion` VALUES ('733', '1', '37', '14c7e6ed-ff42-110b-dc4cbaa2e34f80c5', '', '10', '2023-12-24 11:54:33');
INSERT INTO `tbluserquestion` VALUES ('734', '1', '36', '14c7e6ed-ff42-110b-dc4cbaa2e34f80c5', '', '10', '2023-12-24 11:54:33');
INSERT INTO `tbluserquestion` VALUES ('735', '1', '41', '14c7e6ed-ff42-110b-dc4cbaa2e34f80c5', 'nav', '10', '2023-12-24 11:54:33');
INSERT INTO `tbluserquestion` VALUES ('736', '1', '39', '14c7e6ed-ff42-110b-dc4cbaa2e34f80c5', '', '10', '2023-12-24 11:54:33');
INSERT INTO `tbluserquestion` VALUES ('737', '1', '38', '14c7e6ed-ff42-110b-dc4cbaa2e34f80c5', '', '10', '2023-12-24 11:54:33');
INSERT INTO `tbluserquestion` VALUES ('738', '1', '40', '14d8c247-fa69-7dff-72fc601dadb06df9', '', '10', '2023-12-24 11:56:23');
INSERT INTO `tbluserquestion` VALUES ('739', '1', '38', '14d8c247-fa69-7dff-72fc601dadb06df9', '', '10', '2023-12-24 11:56:23');
INSERT INTO `tbluserquestion` VALUES ('740', '1', '39', '14d8c247-fa69-7dff-72fc601dadb06df9', '', '10', '2023-12-24 11:56:23');
INSERT INTO `tbluserquestion` VALUES ('741', '1', '36', '14d8c247-fa69-7dff-72fc601dadb06df9', '', '10', '2023-12-24 11:56:23');
INSERT INTO `tbluserquestion` VALUES ('742', '1', '41', '14d8c247-fa69-7dff-72fc601dadb06df9', 'async', '10', '2023-12-24 11:56:23');
INSERT INTO `tbluserquestion` VALUES ('1198', '1', '40', 'eba21284-9a25-dbbf-748ab2a62aa3e314', '', '10', '2024-01-02 15:20:02');
INSERT INTO `tbluserquestion` VALUES ('1199', '1', '37', 'eba21284-9a25-dbbf-748ab2a62aa3e314', '', '10', '2024-01-02 15:20:02');
INSERT INTO `tbluserquestion` VALUES ('1200', '1', '38', 'eba21284-9a25-dbbf-748ab2a62aa3e314', '', '10', '2024-01-02 15:20:02');
INSERT INTO `tbluserquestion` VALUES ('1201', '1', '39', 'eba21284-9a25-dbbf-748ab2a62aa3e314', '', '10', '2024-01-02 15:20:02');
INSERT INTO `tbluserquestion` VALUES ('1202', '1', '41', 'eba21284-9a25-dbbf-748ab2a62aa3e314', 'async', '10', '2024-01-02 15:20:02');
INSERT INTO `tbluserquestion` VALUES ('1213', '1', '41', 'effd132a-991e-e99c-236c68dc5f167090', 'async', '10', '2024-01-02 17:21:49');
INSERT INTO `tbluserquestion` VALUES ('1214', '1', '39', 'effd132a-991e-e99c-236c68dc5f167090', '', '10', '2024-01-02 17:21:49');
INSERT INTO `tbluserquestion` VALUES ('1215', '1', '38', 'effd132a-991e-e99c-236c68dc5f167090', '', '10', '2024-01-02 17:21:49');
INSERT INTO `tbluserquestion` VALUES ('1216', '1', '37', 'effd132a-991e-e99c-236c68dc5f167090', '', '10', '2024-01-02 17:21:49');
INSERT INTO `tbluserquestion` VALUES ('1217', '1', '36', 'effd132a-991e-e99c-236c68dc5f167090', '', '10', '2024-01-02 17:21:49');
INSERT INTO `tbluserquestion` VALUES ('1218', '1', '39', 'f01edf85-c805-dfa1-c0f9d8ece9b2bf20', '', '10', '2024-01-02 17:25:31');
INSERT INTO `tbluserquestion` VALUES ('1219', '1', '36', 'f01edf85-c805-dfa1-c0f9d8ece9b2bf20', '', '10', '2024-01-02 17:25:31');
INSERT INTO `tbluserquestion` VALUES ('1220', '1', '38', 'f01edf85-c805-dfa1-c0f9d8ece9b2bf20', '', '10', '2024-01-02 17:25:31');
INSERT INTO `tbluserquestion` VALUES ('1221', '1', '37', 'f01edf85-c805-dfa1-c0f9d8ece9b2bf20', '', '10', '2024-01-02 17:25:31');
INSERT INTO `tbluserquestion` VALUES ('1222', '1', '41', 'f01edf85-c805-dfa1-c0f9d8ece9b2bf20', 'async', '10', '2024-01-02 17:25:31');
INSERT INTO `tbluserquestion` VALUES ('1313', '1', '45', 'f535c6d7-c4c9-1006-ef99f2bffb0df9d7', '', '10', '2024-01-02 19:47:49');
INSERT INTO `tbluserquestion` VALUES ('1314', '1', '47', 'f535c6d7-c4c9-1006-ef99f2bffb0df9d7', '', '10', '2024-01-02 19:47:49');
INSERT INTO `tbluserquestion` VALUES ('1315', '1', '42', 'f535c6d7-c4c9-1006-ef99f2bffb0df9d7', '', '10', '2024-01-02 19:47:49');
INSERT INTO `tbluserquestion` VALUES ('1316', '1', '46', 'f535c6d7-c4c9-1006-ef99f2bffb0df9d7', '', '10', '2024-01-02 19:47:49');
INSERT INTO `tbluserquestion` VALUES ('1317', '1', '43', 'f535c6d7-c4c9-1006-ef99f2bffb0df9d7', '', '10', '2024-01-02 19:47:49');
INSERT INTO `tbluserquestion` VALUES ('1328', '1', '46', 'f57b0d9a-972a-61c3-84f7bc1463a71124', '', '10', '2024-01-02 19:55:23');
INSERT INTO `tbluserquestion` VALUES ('1329', '1', '45', 'f57b0d9a-972a-61c3-84f7bc1463a71124', '', '10', '2024-01-02 19:55:23');
INSERT INTO `tbluserquestion` VALUES ('1330', '1', '44', 'f57b0d9a-972a-61c3-84f7bc1463a71124', 'nav', '10', '2024-01-02 19:55:23');
INSERT INTO `tbluserquestion` VALUES ('1331', '1', '42', 'f57b0d9a-972a-61c3-84f7bc1463a71124', '', '10', '2024-01-02 19:55:23');
INSERT INTO `tbluserquestion` VALUES ('1332', '1', '43', 'f57b0d9a-972a-61c3-84f7bc1463a71124', '', '10', '2024-01-02 19:55:23');
SET FOREIGN_KEY_CHECKS=1;

Objective:

How would I write a SQL query that returns N number of distinct Technical_test_uuid, if the amount of distinct Technical_test_uuids are greater than 10. So, if there are 13 groups of Technical_test_uuid, then 3 distinct Technical_test_uuid would be returned. These 3 records would represent the earliest Technical_test_uuid by Submission_date

There are 13 distinct groups of Technical_test_uuids, in this table.

Query:

This is my attempt:

SELECT Technical_test_uuid
FROM tblUserQuestion uq 
WHERE 
    Technical_test_uuid IN (
        SELECT Technical_test_uuid  
        FROM tblUserQuestion
        WHERE User_ID = uq.User_ID AND Technical_test_uuid IN (
            SELECT DISTINCT(Technical_test_uuid)
            FROM tblUserQuestion
            WHERE User_ID = uq.User_ID
            HAVING COUNT(DISTINCT(Technical_test_uuid)) > 10
        )
    )
GROUP BY uq.Technical_test_uuid

But it only returns one result, when it should return 3

Technical_test_uuid
6f21885b-fe56-0a35-3dd181707f656b08

I would expect the following result:

Technical_test_uuid
6f21885b-fe56-0a35-3dd181707f656b08
6f34ce31-987b-cb1e-a676985c54114a46
ff38ad50-e835-447a-49e220375e3f7769

Please understand that I want to count all the DISTINCT Technical_test_uuid, in the table, and not the amount of Technical_test_uuid, in each group?

Tags:

2

Answers


  1. 0

    See Restrict results to top N rows per group for how to get the top N per group. First get the earliest submission grouped by user ID and test UID. From that, get all except the latest 10 grouped by user ID.

    WITH earliest_by_user_test AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY User_ID, Technical_test_uuid) AS rn
  FROM tbluserquestion
), group_by_user AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY Submission_date DESC) AS rn1
  FROM earliest_by_user_test
  WHERE rn = 1
)

SELECT User_ID, technical_test_uuid
FROM group_by_user
WHERE rn1 > 10
ORDER BY Submission_date;

    DEMO

    Login or Signup to reply.
  2. 0

    We can use a UNION to differentiate the two cases (users whose number of distinct question_uuid>10, and users whose number of distinct question_uuid<=10 ) . But first of all,let’s add 3 more user_id at the end of the original INSERT statements:

    INSERT INTO `tbluserquestion` VALUES ('10023', '2', '36', 'user_2_01', '', '10', '2023-10-17 16:44:28');
INSERT INTO `tbluserquestion` VALUES ('10024', '2', '37', 'user_2_01', '', '10', '2023-10-17 16:44:28');
INSERT INTO `tbluserquestion` VALUES ('10025', '2', '43', 'user_2_02', '', '10', '2023-10-17 16:44:28');
INSERT INTO `tbluserquestion` VALUES ('10026', '2', '41', 'user_2_02', 'async', '10', '2023-10-17 16:44:28');
INSERT INTO `tbluserquestion` VALUES ('10027', '2', '38', 'user_2_03', '', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('10028', '2', '40', 'user_2_03', '', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('10029', '2', '41', 'user_2_03', 'asyncXXXX', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('10030', '3', '42', 'user_3_01', '', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('10031', '3', '45', 'user_3_01', '', '10', '2023-10-17 16:46:34');
INSERT INTO `tbluserquestion` VALUES ('10044', '3', '37', 'user_3_02', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('10045', '3', '39', 'user_3_03', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('10046', '3', '40', 'user_3_04', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('10047', '3', '42', 'user_3_05', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('10048', '3', '43', 'user_3_06', '', '10', '2023-10-25 11:12:37');
INSERT INTO `tbluserquestion` VALUES ('100188', '3', '43', 'user_3_07', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('100189', '3', '45', 'user_3_08', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('100190', '3', '47', 'user_3_09', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('100191', '3', '46', 'user_3_10', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('100192', '3', '42', 'user_3_11', '', '10', '2023-12-22 11:22:01');
INSERT INTO `tbluserquestion` VALUES ('100193', '3', '43', 'user_3_12', '', '10', '2023-12-22 11:23:59');
INSERT INTO `tbluserquestion` VALUES ('100194', '4', '42', 'user_4_01', '', '10', '2023-12-22 11:23:59');
INSERT INTO `tbluserquestion` VALUES ('100195', '4', '44', 'user_4_01', 'nav', '10', '2023-12-22 11:23:59');
INSERT INTO `tbluserquestion` VALUES ('100196', '4', '45', 'user_4_02', '', '10', '2023-12-22 11:23:59');
INSERT INTO `tbluserquestion` VALUES ('100197', '4', '46', 'user_4_02', '', '10', '2023-12-22 11:23:59');

select distinct user_id,technical_test_uuid from tbluserquestion ;
-- result (with user_id 1 and 3 have over 10 distinct test_uuid):
+---------+-------------------------------------+
| user_id | technical_test_uuid                 |
+---------+-------------------------------------+
|       1 | 6f21885b-fe56-0a35-3dd181707f656b08 |
|       1 | 6f34ce31-987b-cb1e-a676985c54114a46 |
|       1 | ff38ad50-e835-447a-49e220375e3f7769 |
|       1 | ac9dad15-f677-8816-50eba2056f77e9ce |
|       1 | acafb668-aa4a-fa1e-a91ab2a0f6005d7f |
|       1 | acbe4c54-974e-5a75-fd0a74fb06c69171 |
|       1 | 14c7e6ed-ff42-110b-dc4cbaa2e34f80c5 |
|       1 | 14d8c247-fa69-7dff-72fc601dadb06df9 |
|       1 | eba21284-9a25-dbbf-748ab2a62aa3e314 |
|       1 | effd132a-991e-e99c-236c68dc5f167090 |
|       1 | f01edf85-c805-dfa1-c0f9d8ece9b2bf20 |
|       1 | f535c6d7-c4c9-1006-ef99f2bffb0df9d7 |
|       1 | f57b0d9a-972a-61c3-84f7bc1463a71124 |
|       2 | user_2_01                           |
|       2 | user_2_02                           |
|       2 | user_2_03                           |
|       3 | user_3_01                           |
|       3 | user_3_02                           |
|       3 | user_3_03                           |
|       3 | user_3_04                           |
|       3 | user_3_05                           |
|       3 | user_3_06                           |
|       3 | user_3_07                           |
|       3 | user_3_08                           |
|       3 | user_3_09                           |
|       3 | user_3_10                           |
|       3 | user_3_11                           |
|       3 | user_3_12                           |
|       4 | user_4_01                           |
|       4 | user_4_02                           |
+---------+-------------------------------------+

    Here is the query to do it. As MySQL 5.5 DOES NOT support window functions, we need to go a long way by using user variables to generate row numbers. It’s a tricky one.

    select * 
from (select distinct user_id,technical_test_uuid
     from tbluserquestion t
     where user_id in (select user_id from tbluserquestion group by user_id having count(distinct technical_test_uuid) <=10)
     group by user_id,technical_test_uuid
     UNION
     select user_id,technical_test_uuid
     from (select user_id,technical_test_uuid,
            if(user_id=@user_id, @row_id:=@row_id+1,@row_id:=1) as row_id,if(user_id=@user_id,@user_id,@user_id:=user_id) as u_id 
            from (select user_id,technical_test_uuid
                  from tbluserquestion
                  where user_id in (select user_id from tbluserquestion group by user_id having count(distinct technical_test_uuid) >10)
                  group by user_id,technical_test_uuid
                  order by user_id,min(submission_date)) t1, (select @user_id:=0,@row_id:=1) t2
            ) tx
     join (select 1 u_id union select 2 union select 3) limit_3
     on tx.row_id=limit_3.u_id
) tbl
order by user_id
;

-- result set:
+---------+-------------------------------------+
| user_id | technical_test_uuid                 |
+---------+-------------------------------------+
|       1 | 6f21885b-fe56-0a35-3dd181707f656b08 |
|       1 | 6f34ce31-987b-cb1e-a676985c54114a46 |
|       1 | ff38ad50-e835-447a-49e220375e3f7769 |
|       2 | user_2_01                           |
|       2 | user_2_02                           |
|       2 | user_2_03                           |
|       3 | user_3_01                           |
|       3 | user_3_02                           |
|       3 | user_3_03                           |
|       4 | user_4_01                           |
|       4 | user_4_02                           |
+---------+-------------------------------------+

    See https://www.db-fiddle.com/f/6S8RTqevYA4UHD8tba24mU/0

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search