How to sort a table by the count of it column?

Question

I have this table:

CREATE TABLE Publications (
    publicationId INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (publicationId),
title VARCHAR(60) NOT NULL UNIQUE,
professorId INT NOT NULL, 
    autors INT NOT NULL,
    magazine VARCHAR(60) NOT NULL,
    post_date DATE NOT NULL, 
    FOREIGN KEY (professorId) REFERENCES Professors (professorId),  
        
    CONSTRAINT invalidPublication UNIQUE (professorId, magazine, post_date),
    CONSTRAINT invalidAutors CHECK (autors >= 1 AND autors <= 10) 
    
    
);

And I want to create a view that returns the professors sorted by the amount of publications they have done, so I have created this view:

CREATE OR REPLACE VIEW ViewTopAutors AS 
SELECT  professorId 
FROM publications
WHERE autors < 5
ORDER by COUNT(professorId)
LIMIT 3;

I've populated the main table, but when I run the view it only returns one autor (the one with the highest Id)

¿How can I do it?

Answer 1

I think an aggregation is missing from your query:

CREATE OR REPLACE VIEW ViewTopAutors AS
SELECT professorId
FROM publications
WHERE autors < 5
GROUP BY professorId
ORDER BY COUNT(*)
LIMIT 3;

This would return the 3 professors with the fewest number of publications. To return professors with the 3 greatest, use a DESC sort in the ORDER BY step.