Display Top X results for each unique value

Question

I am trying to figure out how I can output any machine that has had the latest 3 backups be larger than 10GB. My SQL Table looks similar to the below table:

ComputerID | BackupImageName | WindowsVolumeGUID | FileSize | TimeCreated
1234       | PCBackup.spi    | kjsdfai-dadad-ddd | 10400    | 05-22-20
1234       | PCBackup1.spi   | kjsdfai-dadad-ddd | 10500    | 05-23-20
1234       | PCBackup2.spi   | kjsdfai-dadad-ddd | 10300    | 05-24-20
1234       | PCBackup3.spi   | kjsdfai-dadad-ddd | 10900    | 05-25-20
1225       | PCBackup4.spi   | kjsdfa5-dadad-ddd | 10600    | 05-22-20
1225       | PCBackup5.spi   | kjsdfa5-dadad-ddd | 5000     | 05-23-20
1225       | PCBackup6.spi   | kjsdfa5-dadad-ddd | 10000    | 05-24-20
1225       | PCBackup7.spi   | kjsdfa5-dadad-ddd | 4588     | 05-25-20

I need to output all machines (computerID's) or Volumes (WindowsVolumeGUID) that have their latest 3 files in the list greater than 10000 file size. So from the above table it would output the computerID of 1234 because its latest 3 are greater than 10000 file size and even though the computerID 1225 has 2 files greater than 10000 it would not output the result because there is a break in-between the two large files. The search will need to take into account the WindowsVolumeGUID since some machines have more than one volume and I need to take into account the individual volume backups and count the latest entries based on that.

Alternatively, I can also accomplish the same thing if there is a way to output the top 3 results for each unique WindowsVolumeGUID and not just across the board.

Any help would be greatly appreciated!

Answer 1

1. Incoming schema and test data

   CREATE TABLE `db_info` (
     `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
     `ComputerID` int(10) unsigned NOT NULL,
     `BackupImageName` varchar(32) NOT NULL,
     `WindowsVolumeGUID` varchar(32) NOT NULL,
     `FileSize` int(10) unsigned NOT NULL,
     `TimeCreated` timestamp NOT NULL DEFAULT current_timestamp(),
     PRIMARY KEY (`id`),
     KEY `ComputerID` (`ComputerID`),
     KEY `WindowsVolumeGUID` (`WindowsVolumeGUID`),
     KEY `FileSize` (`FileSize`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   
   INSERT INTO `db_info` (`ComputerID`, `BackupImageName`, `WindowsVolumeGUID`, `FileSize`, `TimeCreated`) VALUES
   (1234, 'PCBackup.spi ', 'kjsdfai-dadad-ddd', 10400, '2020-05-22'),
   (1234, 'PCBackup1.spi', 'kjsdfai-dadad-ddd', 10500, '2020-05-23'),
   (1234, 'PCBackup2.spi', 'kjsdfai-dadad-ddd', 10300, '2020-05-24'),
   (1234, 'PCBackup3.spi', 'kjsdfai-dadad-ddd', 10900, '2020-05-25'),
   (1225, 'PCBackup4.spi', 'kjsdfa5-dadad-ddd', 10600, '2020-05-22'),
   (1225, 'PCBackup5.spi', 'kjsdfa5-dadad-ddd', 5000, '2020-05-23'),
   (1225, 'PCBackup6.spi', 'kjsdfa5-dadad-ddd', 10000, '2020-05-24'),
   (1225, 'PCBackup7.spi', 'kjsdfa5-dadad-ddd', 4588, '2020-05-25');
   

2. Requested Query

   SET @n := 0, @lastComputerId := NULL;
   SELECT ComputerId, SUM(IF(FileSize >= 10000, 1, 0)) AS large_backups_count
   FROM (
       SELECT ComputerId, FileSize
       FROM `db_info`
       WHERE (@n:=(@n * IF(@lastComputerId <=> ComputerID, 1, (@lastComputerId := ComputerID) AND 0) + 1)) < 4
       ORDER BY ComputerID, TimeCreated DESC
   ) AS tmp
   GROUP BY ComputerId
   HAVING large_backups_count >= 3;
   

   Result:

   +------------+---------------------+
   | ComputerId | large_backups_count |
   +------------+---------------------+
   |       1234 |                   3 |
   +------------+---------------------+
   

3. Alternative query based on total size of recent three backups :

   SET @n := 0, @lastComputerId := NULL;
   SELECT ComputerId, SUM(FileSize) AS sum_of_recent_three_backups
   FROM (
       SELECT ComputerId, FileSize
       FROM `db_info`
       WHERE (@n:=(@n * IF(@lastComputerId <=> ComputerID, 1, (@lastComputerId := ComputerID) AND 0) + 1)) < 4
       ORDER BY ComputerID, TimeCreated DESC
   ) AS tmp
   GROUP BY ComputerId
   HAVING sum_of_recent_three_backups >= 30000;
   

   Result:

   +------------+-----------------------------+
   | ComputerId | sum_of_recent_three_backups |
   +------------+-----------------------------+
   |       1234 |                       31200 |
   +------------+-----------------------------+