SQL. Column MAX

Ok, So i have 3 T开发者_StackOverflowables: Movies (Movienum, Title, Yearreleased) Actsin (Movienum, StarID) Stars (StarID, Givenname, Familyname)

• Bold for primary key, italics of foreign

Now I have to find the most recent film Eddie Murphy has starred in and here is my attempt:

SELECT M.Title, M.Yearreleased
FROM Movies M
WHERE M.Yearreleased = (
    SELECT MAX(M.Yearreleased)
    FROM Stars S, ActsIn A, Movies M  
    WHERE S.Givenname = 'Eddie' AND S.Familyname = 'Murphy'
    AND S.StarID = A.StarID
    AND A.MovieNum = M.MovieNum);

This returns all the movies from the same year as the last Eddie Murphy film.

Any help would be amazing :D

How about this:

SELECT M.Title, M.Yearreleased
FROM Stars S, ActsIn A, Movies M  
WHERE S.Givenname = 'Eddie' AND S.Familyname = 'Murphy'
AND S.StarID = A.StarID
AND A.MovieNum = M.MovieNum
ORDER BY Yearreleased Desc
LIMIT 1

Also, on an not so related note, I would consider start using explicit joins, instead of implicit joins. That would make it look like this:

SELECT M.Title, M.Yearreleased
FROM Stars S
INNER JOIN ActsIn A on S.StarID = A.StarID
INNER JOIN Movies M on A.MovieNum = M.MovieNum
WHERE S.Givenname = 'Eddie' AND S.Familyname = 'Murphy'
ORDER BY Yearreleased Desc
LIMIT 1

Trust me, explicit JOIN will make you free when outer joins are needed. And the query is better understandable and readable: joins are joins, filters are filters

You can

ORDER BY Yearrealesed DESC

And if you want just one then LIMIT 1

Link to ORDER BY

EDIT: I Saw you can't use LIMIT, you may wanna check out this old post. (It may help)

Old Post using fetch_assoc()

Also it now appears you aren't using MYSQL. Here is another link, that shoudl give an answer for whatever type of SQL you are using.

Return only X amount of rows