vendredi 22 février 2019

MySQL: How to COUNT data record sets on the base of the highest revision number?

The individual entries in my MySQL 5.7 database enable their revision through PHP: Already saved data records sets can, thus, be actualized. For every single actualization, which is nothing else than saving the same data record set n times, a revision number is auto-incrementally set (it starts with 0):

+----+-----------+-----------+-------------------+----------+
| ID | PatientID | SurgeryID | blahblahblah      | revision |
+----+-----------+-----------+-------------------+----------+
| 1  | 8883      | 7493      | Appendectomy      | 0        |
| 2  | 8883      | 7493      | Appendectomy      | 1        |
| 3  | 8883      | 7493      | Lap. Appendectomy | 2        |
+----+-----------+-----------+-------------------+----------+

The data record set which interests me, of course, is the one with the highest number since it is the latest revision:

+----+-----------+-----------+-------------------+----------+
| ID | PatientID | SurgeryID | blahblahblah      | revision |
+----+-----------+-----------+-------------------+----------+
| 3  | 8883      | 7493      | Lap. Appendectomy | 2        |
+----+-----------+-----------+-------------------+----------+

(Please see that the blahblahblah terms are replaced by numbers (vide infra), I have just termed them here for you to better see the issue.)

How can I COUNT these filtered data record sets?

Done so far:

The SQL query code to filter the highest revision data record set looks like this:

SELECT DISTINCT p.ID, p.PatientID, op.PatID, op.SurgeryID, op.blahblahblah, op.revision
FROM patient_table p
LEFT OUTER JOIN surgery_table op ON op.PatID = p.PatientID
WHERE some restrictions
AND p.PatientID = op.PatID  -- possibly redundant
AND NOT EXISTS (SELECT 1
                  FROM surgery_table op2
                  WHERE op2.PatID = p.PatientID AND op2.revision > op.revision
                 );

This SQL query code works fine and delivers the correct results.

Now I want to COUNT only the various surgery forms performed. My SQL query code so far looks like this:

SELECT COUNT(IF(op.blahblahblah = '0',1,NULL)) 'No Nurgery',
       COUNT(IF(op.blahblahblah = '1',1,NULL)) 'Appendectomy',
       COUNT(IF(op.blahblahblah = '2',1,NULL)) 'Lap. Appendectomy',
       [... lots of surgical procedures listed here ...],
       COUNT(IF(op.blahblahblah = '50',1,NULL)) 'Colostomy',
       COUNT(IF(op.blahblahblah = '99',1,NULL)) 'Different Surgery'
FROM surgery_table op
WHERE op.SurgeryDate BETWEEN "2000-01-01" AND "2020-12-31"

This delivers, of course, ALL data record sets irrespective of their revision numbers.

How can I concatenate or merge or whatever the first and the second SQL query to COUNT merely the data records with the highest revision number?

Aucun commentaire:

Enregistrer un commentaire