[edit]Eve ga.. Prethodno sam mislio da hoces proizvoljan komentar za min i max vrednosti, lol
[/edit]
mysql> select * from t1 order by id, value;
+------+-------+-------+
| id | value | descr |
+------+-------+-------+
| 1 | 8 | var 8 |
| 1 | 24 | var 2 |
| 1 | 97 | var 4 |
| 2 | 12 | var 1 |
| 2 | 73 | var 7 |
| 2 | 102 | var 5 |
+------+-------+-------+
6 rows in set (0.02 sec)
mysql> select q1.id, q1.min, q2.max, s1.descr as 'd_min', s2.descr as 'd_max' from t1 s1, t1 s2, (select id, min(value) as 'min' from t1 group by id order by id) q1, (select id, max(value) as 'max' from t1 group by id order by id) q2 where s1.value=q1.min and s2.value=q2.max and q1.id=q2.id;
+------+------+------+-------+-------+
| id | min | max | d_min | d_max |
+------+------+------+-------+-------+
| 1 | 8 | 97 | var 8 | var 4 |
| 2 | 12 | 102 | var 1 | var 5 |
+------+------+------+-------+-------+
2 rows in set (0.00 sec)
Kôd:
SELECT
q1.id AS 'id',
q1.min AS 'value_min',
q2.max AS 'value_max',
s1.descr AS 'desc_for_min',
s2.descr AS 'desc_for_max'
FROM
`t1` s1,
`t1` s2,
( SELECT id, min(value) AS 'min' FROM `t1` GROUP BY id ORDER BY id ) q1,
( SELECT id, max(value) AS 'max' FROM `t1` GROUP BY id ORDER BY id ) q2
WHERE
s1.value = q1.min
AND s2.value = q2.max
AND q1.id = q2.id