Mr.M Mr.M - 6 months ago 17
SQL Question

MySQL select grouping multiple rows from same table

I'm trying to create a query to select multiple rows from the same table grouping them like an array.
Now i'm selecting them using php like this:

$tks = mysqli_query($con,"SELECT * FROM hof ORDER BY tks DESC LIMIT 5");

$top_ths = mysqli_query($con,"SELECT * FROM hof ORDER BY ths DESC LIMIT 1");

$top_tha = mysqli_query($con,"SELECT * FROM hof ORDER BY tha DESC LIMIT 1");

----


I would like to merge them in a single query so i get an associative array.
Something like this:

(SELECT * FROM hol ORDER BY tks DESC LIMIT 5) AS tks
UNION
(SELECT * FROM hol ORDER BY ths DESC LIMIT 1) AS top_ths


So tks contains all the 5 rows and top_ths contains 1 row.
Is it possible ? Thanks.

Answer

to undersatnd from wich group row is, make additional field

(SELECT *, 1 as `group` FROM hol ORDER BY tks DESC LIMIT 5)
UNION
(SELECT *, 2 as `group` FROM hol ORDER BY ths DESC LIMIT 1)