Error while executing the request

Question

There is a request:

SELECT lichniy_nomer, MAX(`sum`) FROM ( SELECT lichniy_nomer, SUM(narushenie.shtraf)'sum' FROM protocol, narushenie WHERE protocol.nomer_narusheniya=narushenie.nomer_narusheniya GROUP BY (lichniy_nomer))t1 HAVING MAX(`sum`) = (SELECT lichniy_nomer FROM (SELECT lichniy_nomer, MAX(`sum`) FROM ( SELECT lichniy_nomer, SUM(narushenie.shtraf)'sum' FROM protocol, narushenie WHERE protocol.nomer_narusheniya=narushenie.nomer_narusheniya GROUP BY (lichniy_nomer))t1))

As planned, this request should return a personal number (based on the maximum amount). However, an error occurs:

 #1248 - Every derived table must have its own alias.

How to fix, can not understand.

@pixma; If you are given a comprehensive answer, mark it as correct (click on the check mark next to the selected answer).

Yura Ivanov Yura Ivanov 25.2k one 22 52 · Answer 1 · 2014-12-04T15:38:55

Yes, @smackmychi is right, such monstrous constructions are useless here.

 select sum(n.shtraf) `sum`, p.lichniy_nomer from protocol p join narushenie n on n.nomer_narusheniya = p.nomer_narusheniya group by p.lichniy_nomer order by `sum` desc limit 1

Or if you want with attachments:

 select s.`sum`, p.lichniy_nomer from protocol p join (select nomer_narusheniya, sum(shtraf) `sum` from narushenie group by nomer_narusheniya order by `sum` desc limit 1) s on s.nomer_narusheniya = p.nomer_narusheniya

Threat Error # 1248 says that all subqueries must have aliases ... which is logical and neat.

Yura Ivanov

25.2k one 22 52

I did not check it, but experience suggests that the second option will work much faster than the first. Therefore, there is not even "if you want," but "it is better to do so." The first option is to join им таблицы, потом группируем, потом сортируем и только потом выводим ответ. Второй же вариант - группируем, сортируем, возвращаем одну строку, по которой выполняем join. Были случаи, когда сортировка не по индексу на join им таблицы, потом группируем, потом сортируем и только потом выводим ответ. Второй же вариант - группируем, сортируем, возвращаем одну строку, по которой выполняем join. Были случаи, когда сортировка не по индексу на join им таблицы, потом группируем, потом сортируем и только потом выводим ответ. Второй же вариант - группируем, сортируем, возвращаем одну строку, по которой выполняем join. Были случаи, когда сортировка не по индексу на join e significantly worsened the time (from a split second to a couple of minutes). Those. grouping and sorting in the second version will work faster, and the join by one value and for the join can not be considered) - BOPOH
@BOPOH, not better. until statistics are collected and quantitative indicators are not announced on the tables, it’s too early to talk about optimization. in this case we are talking about the simplicity of the request, and not about the speed, so both options are possible. about optimization, we are waiting for the next questions from the vehicle. - Yura Ivanov

|

Answer 2 · 2014-12-04T13:45:25

For example. Request:
SELECT id, sum(amount) FROM table group by ID order by 2 desc limit 1;
Return one record (limit 1) - identifier and amount. This sample is grouped by ID (group by ID) and sorted (order by) by descending (desc) of the second column (order by 2), so the first record will have the maximum amount of the sum and the corresponding identifier. I have no practical experience in this area, you can wait for a response from someone else.

Get get 3.432 five 7 · Answer 3 · 2014-12-04T16:17:41

1248 - Every derived table.
Google translate: 1248 - Each derived table must have its own nickname.

@Yura Ivanov wrote everything correctly, you should not write such scary queries for simple tasks.

Error while executing the request

3 answers 3

More articles: