SQL retrieves data from the column with the most rows in

Question

I have a table called movies and a column called movie_id . And I want to get the most common movie_id in the table.

Example data in a column:

 movie_id 234 343 2928 956 73 234 234 

So, the result should be 234, because it has the largest number of rows. I have never tried this, so I really don't know how this can be achieved.

Answer 1

depending on sql there might be something like

 SELECT * FROM movies WHERE movie_id=(SELECT movie_id FROM movies GROUP BY movie_id ORDER BY count(*) DESC LIMIT 1) 

that is, if I understand correctly what you are looking for.

Answer 2

For MySQL:

 SELECT movie_id FROM movies GROUP BY movie_id ORDER BY COUNT(*) DESC LIMIT 1 

For SQL Server:

 SELECT TOP 1 movie_id FROM movies GROUP BY movie_id ORDER BY COUNT(*) DESC 

If you want all related (top) movies to be returned:

 SELECT movie_id FROM movies GROUP BY movie_id HAVING COUNT(*) = ( SELECT TOP 1 COUNT(*) FROM movies GROUP BY movie_id ORDER BY COUNT(*) DESC ) 

Answer 3

 SELECT movie_id FROM ( SELECT movie_id, count(*) AS num FROM <tablename> GROUP BY movie_id ) AS baseview ORDER BY num DESC LIMIT 1 

Answer 4

For SQL Server use:

 SELECT TOP 1 COUNT(*) as cnt FROM movies GROUP BY(movie_id) ORDER BY cnt DESC