Извлечение строк для для которых поле amount в группе имеет максимальное значение.

[Гриша К.]

Извлечение строк для для которых поле amount в группе имеет максимальное значение.

Добрый вечер. Пытался найти ответ в поиске, не получилось. Подскажите пожалуйста решение.

К пример есть такая таблица:
orders (productid, foto, signature, amount, date)

|-----------|--------|-----------|--------|------|
| productid |  foto  | signature | amount | date |
|-----------|--------|-----------|--------|------|
|    1      | 10.jpg | Kolbosa   | 70.00  | 2006 |
|    1      | 8.jpg  | Sosiska   | 50.00  | 2005 | 
|    1      | 7.jpg  | Shpinat   | 100.00 | 2004 |
|    4      | 2.jpg  | Mokoron   | 50.00  | 2006 |
|-----------|--------|-----------|--------|------|

Я объединяю данные в группы по стоблцу 'productid' и пытаюсь извлеч строки, для которых столбец 'amount' максимальный:

$query = "select productid, foto, signature, MAX(amount), date
                 from orders
                 where GROUP BY productid";

Вывод получается такой:

|-----------|--------|-----------|--------|------|
| productid |  foto  | signature | amount | date |
|-----------|--------|-----------|--------|------|
|    1      | 10.jpg | Kolbosa   | 100.00 | 2006 |
|    4      | 2.jpg  | Mokoron   | 50.00  | 2006 |
|-----------|--------|-----------|--------|------|

Т.е. когда я ставлю MAX(amount), извлекается только максимальное значение amount, а остальные строки не меняются. А мне надо вывести все строки, у которых 'amount' есть максимальное значение.

Результат я хочу получить такой:

|-----------|--------|-----------|--------|------|
| productid |  foto  | signature | amount | date |
|-----------|--------|-----------|--------|------|
|    1      | 7.jpg  | Shpinat   | 100.00 | 2004 |
|    4      | 2.jpg  | Mokoron   | 50.00  | 2006 |
|-----------|--------|-----------|--------|------|

 

[_RVK_]

Не получится таким образом. Потому, что функция MAX никак не влияет на сортировку строк. [... здесь сказал глупость...]

 

[Гриша К.]

_RVK_,
я пробовал делать так:

$query = "select productid, foto, signature, amount, date 
                 from orders 
                 where GROUP BY productid HAVING MAX(amount)";

Но так вообще ничего не извлекатеся, видно я что-то не то делаю.

 

[_RVK_]

Гриша К.
HAVING это условие подобное WHERE только работающее после GROUP BY.

 

[Гриша К.]

Даже незнаю как правильно сделать, попробовал вот так:

SELECT productid, foto, signature, amount, date 
	FROM orders
	GROUP BY productid HAVING amount = MAX(amount)

Но так извлекается вообще самое максимальное значение из таблицы, а мне нужно максимльное в группе GROUP BY productid.

-~{}~ 25.03.06 01:07:

_RVK_, я прочитал что [HAVING определение_where] в отличие от where может ссылатьтся на агрегатные функции.
И вот теперь немогу понять как извлеч строки с маскимальным значениеме MAX(amount) для группы GROUP BY productid

 

[itprog]

не помню с какой версии вложенные запросы...

$query = "select productid, foto, signature, amount, date
                 from orders
                 where amount in (select MAX(amount) from orders
                 where GROUP BY productid)";

 

[Гриша К.]

В другой книге (Люк Веллинг и Лора Томсон), я прочитал, что конструкция HAVING используется именно для групп, но какое же нужно задать условие в HAVING чтобы извлекались строки для которых amoount имееь максимальное значение в группе, скажите пожалуйста, я совсем запутался, уже 2,5 часа пытаюсь разобраться с этим выводом.

-~{}~ 25.03.06 01:17:

itprog, спасибо за ответ. Но вложенные запросы с версии 4.1, у меня на хостинге 4.0...

 

[Andreika]

itprog
даж если вложенные подзапросы есть - запрос вернет не то, что нужно

Гриша К.
http://dev.mysql.com/doc/refman/4.0/ru/example-maximum-column-group-row.html

 

[itprog]

Гриша К.
Ясно, один вопрос, почему в "Результат я хочу получить такой" нет
| 1 | 8.jpg | Sosiska | 50.00 | 2005 |
?

 

[Гриша К.]

itprog, потому что мне нужно извлеч из всех строк где productid = 1, ту строку, в которой amount самый большой

-~{}~ 25.03.06 02:48:

Andreika, спасибо вам большое, вы очень сильно мне помогли.
Я даже и не думал, что запрос будет такой сложный.
В итоге все получилось.
? Толькое еще хотел узнать, не является ли создание такой временной таблиц, какой-то большой нагрузкой на БД, и что например не рекомендуется его использовать или это просто обычная таблица и все. Просто я подумал, что если эту страницу будут часто посещать и слздание временной таблицы будет производиться много раз, ничего страшного не будет, это нормально. ?

@mysql_query ("CREATE TEMPORARY TABLE tmp (
	productid char(2) NOT NULL default '',
	amount decimal(10,2) default NULL)");
@mysql_query ("LOCK TABLES orders read");
@mysql_query ("INSERT INTO tmp SELECT productid, MAX(amount) FROM orders GROUP BY productid");

$query = "SELECT orders.productid, orders.foto, orders.signature, orders.amount, orders.date 
	FROM orders, tmp 
	where orders.productid = tmp.productid and orders.amount = tmp.amount and orders.worldid";
$result = @mysql_query($query);

if (!$result || @mysql_num_rows($result) == 0) {echo 1;
	$foto_array = '';
}
else {  
	$foto_array = db_result_to_array($result);
}

@mysql_query ('UNLOCK TABLES');
@mysql_query ('DROP TABLE tmp');