Search code examples
phpmysqlmultiple-select

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.

Solution

  • 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)