How to Retain Multiple Results from a MySQL CTE query?-CodePudding

I am trying to create a pre-process to compute the lower and upper interquartile gap values and save them as variables to use in a subsequent WHERE clause. I borrowed some code and can get one of the two values retained, but not both as I keep running into a problem with crafting a single SELECT that runs as the object of the CTE. I have been hacking on this for two days and have still not figured out a way to do this (I am not a MySQL expert). Can anyone help me correct the code to get both Lower and Upper variables saved? To have this example actually run, I have commented out one of the variables to save, but I need to get both. Thanks for any help!

SET @lowlim=0;
SET @upplim=0;
with orderedList AS (
SELECT
    full_name,
    age,
    ROW_NUMBER() OVER (ORDER BY age) AS row_n
FROM friends
),
iqr AS (
SELECT
    age,
    full_name,
    (
        SELECT age AS quartile_break
        FROM orderedList
        WHERE row_n = FLOOR((SELECT COUNT(*)
            FROM friends)*0.75)
            ) AS q_three,
    (
        SELECT age AS quartile_break
        FROM orderedList
        WHERE row_n = FLOOR((SELECT COUNT(*)
            FROM friends)*0.25)
            ) AS q_one,
    1.5 * ((
        SELECT age AS quartile_break
        FROM orderedList
        WHERE row_n = FLOOR((SELECT COUNT(*)
            FROM friends)*0.75)
            ) - (
            SELECT age AS quartile_break
            FROM orderedList
            WHERE row_n = FLOOR((SELECT COUNT(*)
                FROM friends)*0.25)
            )) AS outlier_range
    FROM orderedList
)

--  SELECT ((SELECT MAX(q_one)
--  FROM iqr) -
--  (SELECT MAX(outlier_range)
--      FROM iqr)) AS lower_limit
-- INTO @lowlim
-- FROM iqr LIMIT 1
 SELECT ((SELECT MAX(q_three)
    FROM iqr)  
    (SELECT MAX(outlier_range)
        FROM iqr)) AS upper_limit
INTO @upplim
FROM iqr LIMIT 1;

SELECT @lowlim, @upplim;

EDITs: Here is the website I got the original code from and the example data table.

Code to create sample data:

CREATE TABLE IF NOT EXISTS `friends` (
  `full_name` text,
  `age` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping data for table TFG.friends: ~0 rows (approximately)
/*!40000 ALTER TABLE `friends` DISABLE KEYS */;
INSERT INTO `friends` (`full_name`, `age`) VALUES
    ('Evangeline', 21),
    ('Amelia', 29),
    ('Marie', 30),
    ('Kiara', 31),
    ('Isobel', 31),
    ('Jane', 32),
    ('Genevieve', 32),
    ('Chloe', 33),
    ('Phillip', 33),
    ('Morgan', 33),
    ('Jeremy', 34),
    ('Claudia', 35),
    ('Sonny', 57),
    ('Frazer', 64),
    ('Sarah', 64),
    ('Frankie', 65);
/*!40000 ALTER TABLE `friends` ENABLE KEYS */;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;

CodePudding user response：

You should be able to do this with window functions:

WITH ...your cte definitions...

SELECT MAX(q_one) OVER () - MAX(outlier_range) OVER () AS lower_limit,
    MAX(q_three) OVER ()   MAX(outlier_range) OVER () AS upper_limit
FROM iqr
LIMIT 1

Output given your test data, I tested it on MySQL 8.0.29:

 ------------- ------------- 
| lower_limit | upper_limit |
 ------------- ------------- 
|        25.0 |        41.0 |
 ------------- -------------

Re your comment:

My habit is to just the query return a result set, fetch it into variables in my application code, and then use those variables as parameters to subsequent queries.

But if you really need to set MySQL user-defined variables, you can use INTO syntax for multiple columns:

SELECT <expr1>, <expr2> INTO @a, @b FROM ...

See more examples in the manual: https://dev.mysql.com/doc/refman/8.0/en/select-into.html