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?
2
Answers
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.
DEMO
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:
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.
See https://www.db-fiddle.com/f/6S8RTqevYA4UHD8tba24mU/0