There is such a simple sign.

alt text

The task is to create a list of disciplines with the maximum number of semesters. The question is, suppose I am writing, to begin with, this is the query:

SELECT [name_subject] ,COUNT(*) AS [semesters] FROM [subject] GROUP BY [name_subject]

And now I want to find the maximum number of semesters for a particular discipline (in our case 2). Writing:

 SELECT MAX([semesters]) FROM ( подзапрос выше )

But for some reason, the error Invalid column name 'semesters' . Is it possible to somehow select certain columns from a table that is itself generated by another select?

Thank you in advance.



    1 answer 1

    It is enough to assign a pseudonym to the subquery:

     select max(a.semesters) from ( select name_subject, count(*) as semesters from subject group by name_subject ) as a group by a.name_subject;

    and then you can refer to the field names of the subquery.