MySQL - Vyber riadkov s MAX hodnotou v jednom stlpci – MySQL – Fórum – Programujte.com

| skupina | hodnota | data1 | data2 | data...

+---------+---------+-------+----------------

|      1  |      5  |  A    | ..... | .

|      1  |     10  |  B    | ....

|      2  |      7  |  C    | ...

|      2  |     12  |  D    | .

| skupina | hodnota | data1 | data2 | data...

+---------+---------+-------+----------------

|      1  |     10  |  B    | ....

|      2  |     12  |  D    | .

SELECT T1.* 

FROM tabulka AS T1

     INNER JOIN (SELECT skupina,MAX(hodnota) AS max_hodnota

                 FROM tabulka GROUP BY skupina) AS T2

     ON 

        T1.skupina = T2.skupina 

        AND 

        T1.hodnota=T2.max_hodnota

SELECT *

FROM   (

        SELECT   * 

        FROM     tabulka 

        ORDER BY skupina, hodnota DESC

       ) AS T1

GROUP BY skupina

Neviem ci je to zlozitejsie alebo nie ale urcite to funguje

<?php
$result = mysql_query ("SELECT distinct(skupina) FROM test");
echo mysql_error();
$i=0;
while ($row = mysql_fetch_array ($result))
{
$array[$i] = $row["skupina"];
$i++;
}
$i = NULL;

foreach ($array as $value)
{
echo "Skupina: ".$value." ";
$result = mysql_query ("SELECT hodnota, data FROM test WHERE skupina = '$value' ORDER BY hodnota DESC limit 0,1");

while ($row = mysql_fetch_array ($result))
{
echo $row["hodnota"]." ".$row["data"]."";

}
}
mysql_close($link);
?>

To JannyM : Som rád že sa aspoň niekto ozval. No, chcem jedno rýchle a pamäťovo nenáročné query, lebo tvojim postupom zaťažím aj PHP a zároveň aj databázu množstvom požiadaviek...


Len som myslel, či na tento problém neexistuje nejaké primitívne, univerzálne riešenie...

ten INNER JOIN som prepísal na WHERE, DESCRIBE vypíše presne to isté, ale zápis query je kratší a vyzerá čitateľnejšie :-)

Ak sa k tomu niekto náhodou dostane, tak sa prosím pozrite na:

DESCRIBE spomínaných query, raz je tabuľka bez indexov, raz je primary index na (skupina, hodnota):

Nerozumiem prečo sa v tom subquery (query 2 s indexom) nepoužil index, aspoň v tom zoradení. (prečo je tam using filesort ???) Vyplýva z toho všetkého, že najrozumňejšie je použiť práve ten prvý query, ktorý ten index zoberie ?

Prípadne, fakt MySQL nemá triviálny spôsob na vykonanie takého selectu ???


Query 1 - [tabľka] join [SELECT skupina, max(hodnota)]

SELECT T1.* 

FROM tabulka AS T1, 

     (SELECT skupina, max(hodnota) as max_hodnota 

      FROM tabulka GROUP BY skupina) AS T2

WHERE 

     T1.skupina = T2.skupina 

     AND 

     T1.hodnota = T2.max_hodnota;



# pre tabuľku bez indexov:

+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+

| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                           |

+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+

|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    2 |                                 |

|  1 | PRIMARY     | T1         | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where                     |

|  2 | DERIVED     | tabulka    | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using temporary; Using filesort |

+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+



# s indexom:

+----+-------------+------------+--------+---------------+---------+---------+---------------------------+------+-------------+

| id | select_type | table      | type   | possible_keys | key     | key_len | ref                       | rows | Extra       |

+----+-------------+------------+--------+---------------+---------+---------+---------------------------+------+-------------+

|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL                      |    2 |             |

|  1 | PRIMARY     | T1         | eq_ref | PRIMARY       | PRIMARY | 8       | T2.skupina,T2.max_hodnota |    1 |             |

|  2 | DERIVED     | tabulka    | index  | NULL          | PRIMARY | 8       | NULL                      |    4 | Using index |

+----+-------------+------------+--------+---------------+---------+---------+---------------------------+------+-------------+

SELECT *

FROM   (

        SELECT   * 

        FROM     tabulka 

        ORDER BY skupina, hodnota DESC

       ) AS T1

GROUP BY skupina;



# pre tabuľku bez indexov:

+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+

| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                           |

+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+

|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using temporary; Using filesort |

|  2 | DERIVED     | tabulka    | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using filesort                  |

+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+



# s indexom:

+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+

| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                           |

+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+

|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using temporary; Using filesort |

|  2 | DERIVED     | tabulka    | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using filesort                  |

+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+

Hej viem len som chel skusit ci to dokazem urobit trochu inak a podarilo sa tak preco to nezverejnit :) ale efektivnost kodu e ZERO :)