0

I'm looking for a way to combine multiple queries that each need two values.

For example, I can look up a patient's age and weight as follows:

select age
     , weight 
  from patients 
  join visits on patientID = patientNum 
 where visits.patientNum = 1;
+------+--------+
| age  | weight |
+------+--------+
|   15 |   3.64 |
|   41 |      5 |
|  114 |   6.98 |

The problem is that I need to perform a query on each row to get their percentile; for example, taking the first row from above:

select age
     , percentile
     , abs(weight-3.64) as value 
  from Weights 
 where age = 15 
 order by value 
 limit 1;
+-----+------------+-----------------------+
| age | percentile | val                   |
+-----+------------+-----------------------+
|  15 |         39 | 0.0052000141143797585 |
+-----+------------+-----------------------+

The desired output would be:

+-----+------------+-----------------------+
| age | percentile | val                   |
+-----+------------+-----------------------+
|  15 |         39 | 0.0052000141143797585 |
|  41 |         83 | 0.0099000930786132810 |
| 114 |         90 | 0.0121998596191410510 |
+-----+------------+-----------------------+

I could trivially preform three queries for this example, but I need a way to do this all at once.

  • Your schema is unclear. Please edit the question and post the CREATE TABLE statements of the tables, some sample data as INSERT INTO statements and the desired result with that sample data. – sticky bit Apr 7 at 14:00
  • MySQL 8.0 has percentiles, as does MariaDB. – Rick James Apr 22 at 1:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.