SQL. Column MAX

Question

Ok, So i have 3 Tables: 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

actually, if Murphy has starred in several movies this year, you can't get exactly last one movie but all the movies with Murphy released last year. You have to store date released. — pupssman, Sep 15 '11 at 13:43
is this homework? Sounds very similar to an Oracle question... — Bruce, Sep 15 '11 at 13:45
Its for Uni. The only movies for murphy in the DB are from 3 seperate years. — Steve, Sep 15 '11 at 13:47
Well for oracle use WHERE ROWNUM <= 1, I added a link to my answer as well, for returning X rows for whatever SQL type you are using. — sealz, Sep 15 '11 at 13:48

score 3 · Answer 1 · answered Sep 15 '11 at 13:31

3

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

answered Sep 15 '11 at 13:31

Adriano Carneiro

57,693
12
90
123

unfortunately LIMIT is not supported by the DBMS I have to use for uni. which is :(. – Steve Sep 15 '11 at 13:35
SQLFIRE. it give me this error " Token Unknown - LIMIT" cheers for the tip on explicit joins :) – Steve Sep 15 '11 at 13:38
2

Your tag said mySQL, which LIMIT x is a valid clause in. If you are using Microsoft SQL, you can use TOP x instead.... (Select top 1 ...) – Sparky Sep 15 '11 at 13:40

score 1 · Accepted Answer · edited May 23 '17 at 10:34

1

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

edited May 23 '17 at 10:34

Community

1
1

answered Sep 15 '11 at 13:32

sealz

5,348
5
40
70

that second link :D:D:D:D. Thank you so much. it was SELECT first yada yada yada. Thanks again – Steve Sep 15 '11 at 13:49

SQL. Column MAX

2 Answers2