enter image description here

Query: display the most popular song. I understand, you need in the Record table to count the number of each song and display the maximum. But I can not understand how to write a request. There you need to use a subquery with 2 count? And group by songs?

Closed due to the fact that off-topic participants aleksandr barakin , HamSter , fori1ton , Alexander Petrov , iluxa1810 13 Nov '16 at 11:41 .

  • Most likely, this question does not correspond to the subject of Stack Overflow in Russian, according to the rules described in the certificate .
If the question can be reformulated according to the rules set out in the certificate , edit it .

  • four
    This question-picture should be closed, because it is useless for the knowledge base: searching for the drawn text (for now) is impossible. - aleksandr barakin

2 answers 2

Something like this

select top 1 a.[Код], a.[Название], count(*) cnt from [Заказы] o join [Запись] r on r.[Код] = o.[Код записи] join [Архив песен] a on a.[Код] = r.[Код песни] group by a.[Код], a.[Название] order by cnt desc

I can be mistaken because I do not know the features of access, but in general it should be correct

  • In Access, this will not work. 1) It is necessary to write INNER JOIN, 2) When you do 2 JOIN in a row, there is a tricky framing of brackets. - iluxa1810 5:53

I think something like:

 select top 1 * from ( Select [Код песни],COUNT(*) as cnt from [Запись] Group by [Код песни]) t order by cnt desc

But consider that Access is a thing = => request probably it is necessary to optimize.

PS It is wrong to call database objects with Russian names, as well as use words with spaces.

Is it convenient for you to change the layout from English to Russian and back when writing a request, as well as constantly escaping [] double names?

UPD

Here is the conclusion of the song title:

 select [название] from [Архив песен] a INNER JOIN (select top 1 * from ( Select [Код песни],COUNT(*) as cnt from [Запись] Group by [Код песни]) t order by cnt desc) t2 on a.[код]=t2.[Код песни]

PS Learn SQL. I see that you have no basic concepts at all ...

  • I will not say that I enjoy it. - babls 2:22
  • Can you explain the request? - babls
  • @babls, in the subquery we get the number of the song and the number of occurrences, and in the external one we sort in descending order and take only 1 line. - iluxa1810
  • What does t mean? - babls 2:26
  • @babls, alias to the subquery. Without this, the request does not work. - iluxa1810