CODEWITHSUNDEEP
sql
randdotname
randdotname

Reputation: 15

Finding 1 MAX value in query

I need to find the longest movie and only print the title of that movie. However, when I try to do it, it just prints the title of every movie and all their lengths. So i'd like to know what I am doing wrong.

SELECT m.movie_title, MAX(m.movie_len)
FROM movie m
GROUP BY m.movie_title;

Upvotes: 0

Views: 31

Answers (2)

Adam Silenko
Adam Silenko

Reputation: 3108

you must have clause where to limit rows, or use your query as sub query or if your db engine support use "limit 1 like Gordon Linoff writ, or select top 1 like in sql-serwer, or first over like in oracle... you didn't write db engine name...

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270463

One method uses order by and limit:

select m.*
from movie m
order by length desc
limit 1;

MAX() is a function that operates on one column. It has no effect on other columns.

Upvotes: 2

Related Questions