you have to be a little tricky at this point.
first you create a temporary table for your select, this happens in the example in
CALL my_procedure():
Then you can use that temporary table and use it in your query, and finally you can DROP the temporary table.
Schema (MySQL v8.0)
CREATE TABLE example (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
fullname VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL,
something VARCHAR(255) NOT NULL
);
INSERT INTO example (id, fullname, email, something) VALUES
(1, 'Name 1', '[email protected]', 'Something 1'),
(2, 'Name 2', '', 'Something 2'),
(3, 'Name 1', '[email protected]', 'Something 3'),
(4, 'Name 3', '[email protected]', 'Something 4'),
(5, 'Name 3', '[email protected]', 'Something 5'),
(6, 'Name 4', '', 'Something 6');
delimiter $$
CREATE PROCEDURE my_procedure ()
BEGIN
CREATE TEMPORARY TABLE new_tbl
SELECT MAX(fullname), email
FROM example
GROUP BY email ;
END $$
delimiter ;
delimiter $$
CREATE PROCEDURE my_procedure2 ()
BEGIN
SELECT * FROM new_tbl;
END $$
delimiter ;
delimiter $$
CREATE PROCEDURE my_procedure3 ()
BEGIN
SELECT * FROM new_tbl;
#DROP TEMPORARY TABLE IF EXISTS new_tbl;
END $$
delimiter ;
Query #1
CALL my_procedure();
There are no results to be displayed.
Query #2
call my_procedure2();
| MAX(fullname) | email |
| ------------- | ----------------- |
| Name 1 | [email protected] |
| Name 4 | |
| Name 3 | [email protected] |
Query #3
call my_procedure3();
| MAX(fullname) | email |
| ------------- | ----------------- |
| Name 1 | [email protected] |
| Name 4 | |
| Name 3 | [email protected] |
View on DB Fiddle