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.