Home sql SQL query, output MAX (count (...)) and other fields of the table...

SQL query, output MAX (count (…)) and other fields of the table corresponding to the MAX-parameter

Author

Date

Category

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.

Programmers, Start Your Engines!

Why spend time searching for the correct question and then entering your answer when you can find it in a second? That's what CompuTicket is all about! Here you'll find thousands of questions and answers from hundreds of computer languages.

Recent questions