The following tables are available:
person (fields nom, etc.) - information about people PROFIT (fields ID, Source, MoneyS) - sources of income, Have_d (fields NOM, ID, etc.) - the relationship between people and their income.
Everyone can have several sources of income.
It is necessary to withdraw all information about the most popular income source. That is, it is necessary to calculate the number of inclusions of all types of income, select the maximum and output the resulting number together with all fields of the
profit table, corresponding to the resulting maximum.
I was able to output the maximum number, but it is impossible to make a request for the output of the line from
Profit , it is appropriate.
select max (exp1) From (Select Count (NOM) AS EXPR1 From Profit, Have_d, Person Where profit.id = have_d.id and have_d.nom = person.nom GROUP by Source)
Answer 1, Authority 100%
The problem is known. 🙂
here you will find a solution.
Answer 2, AUTHORITY 60%
Thanks for the link. Now the request looks like this:
select top 1 t1. * From (Select Profit.source, Count (*) AS EXPR1 From Profit, Have_D Where profit.id = have_d.id group by profit.source) as t1 Order by Expr1 Desc
Answer 3, Authority 20%
select t1. * From (Select Profit. *, Count (*) AS EXPR1 From Profit, Have_D Where profit.id = have_d.id Group by profit.id. Order by Expr1 Desc) AS T1 Limit 1.
I am sure there is a better solution (let’s say without Limit). Table
Person Not needed.