There is a label with the fields id - PK AI, event - the name of the event, year - the year in which the event occurred:

 events: id | event | year 1 | X | 2000 2 | X | 2000 3 | Y | 2000 4 | Z | 2001 6 | T | 2002

1) It is necessary to bring the most popular events in the most popular year. Those. in this case, the most popular year is 2000, because 3 events happened in it at once. And the most popular event in 2000 is X.

The result should be in the form: event, number, year .

 event | cnt | year X | 2 | 2000

2) If there are several most popular events, then you need to display all the most popular events in the most popular year. For example for:

 id | event | year 1 | X | 2000 3 | Y | 2000 4 | Z | 2001 6 | T | 2002

The sql query should return 2 rows:

 event | cnt | year X | 1 | 2000 Y | 1 | 2000

3) Write a sql query that would display the most popular events in the most popular years. Those. may be some of the most popular years. For example:

 id | event | year 1 | X | 2000 3 | Y | 2000 4 | Z | 2001 6 | T | 2001 7 | W | 2005

The result should be:

 event | cnt | year X | 1 | 2000 Y | 1 | 2000 Z | 1 | 2001 T | 1 | 2001

1) My version is that the first query can be written as (it does not work correctly on all data):

 select event, count(*) cnt, year from events group by year, event order by cnt desc limit 1;
  • How to fix query for option 1?
  • How to write a sql query correctly so that it works for options 2 and 3?
  • You have a request for option 1 by the way is not correct. If in 2000 we say a couple more events with completely different letters, and for 2002 there will be 2 more T events, then your request will return the T 2002 event since there will be 3 of them. And for the most popular year 2000 of identical events (X), only 2 - Mike
  • @Mike really. - jekaby

1 answer 1

one.

 select event, year, count(1) cnt from events where year=(select year from events group by year order by count(1) desc limit 1) group by event, year order by cnt desc limit 1

2

 select event, year, count(1) cnt from events group by event, year having (year,count(1))= ( select year, count(1) from events where year =(select year from events group by year order by count(1) desc limit 1) group by event, year order by count(1) desc limit 1 )

3

 select event, year, count(1) cnt from events group by event, year having (year,count(1)) in ( select year, max(cnt) m from ( select year, event,count(1) cnt from events group by year, event ) A group by year having sum(cnt)=( select count(1) cnt from events group by year order by count(1) desc limit 1 ) )

And lovers to break their heads with MySQL variables can take advantage of this option (perhaps more quickly, in the case when there are many similar events):

 select year,event,cnt from ( select *,@yc:=cnt+if(year=@y,@yc,0) yc, @mcnt:=if(year!=@y,cnt,@mcnt) mcnt, @yl:=if(@yc>@myc,year,if(@yc=@myc,concat(@yl,',',year),@yl)), @myc:=if(@myc<@yc,@yc,@myc), @y:=year from ( select year, event,count(1) cnt from events group by year, event ) A,(select @yc:=0,@y:=0,@mcnt:=0,@yl:='',@myc:=0) B order by year,cnt desc ) A where find_in_set(year,@yl)>0 and cnt=mcnt
  • if I'm not stupid, the last option and, accordingly, the request should always work - splash58
  • Maybe. It depends on what is considered the "most popular year" if there are several such years. I prefer to assume that one is needed anyway, then options 1 and 2 will be needed. Well, you can see from them how the thought moves when building option 3 :) - Mike
  • @Mike Thank you. All options work as it should! Could you please clarify this entry: having (year,count(1)) in - how does it work? - jekaby
  • @jekaby I think the usual A in(select B) you know, which selects all the A which gives it a subquery. (A,B) in(select C,D) gives all records where pairs of columns are equal, i.e. records where A=C and B=D In our case, the subquery chooses year, max (cnt), i.e. pairs YEAR - max_call_ of the same_ events And having already leaves the upper group of entries for which the specified years And the number of events is equal to the listed subquery - Mike
  • I thought so, I just never used such a technique having (A, B) .. I expected it to work out with more simple queries. OK thanks! - jekaby