hamada147 hamada147 - 3 months ago 9
SQL Question

How to select where two columns are MAXIMUM

I've created a view in order to keep using it without having to write this part of code each time the view called "Data"

The first SQL statement will get me the max season for each series for specific user.

SELECT s_imdbID, MAX(ep_season) FROM Data
WHERE u_ID = 1
GROUP BY s_imdbID


The second SQL statement will get me the max episode of latest season for specific series for a specific user.

SELECT s_imdbID, ep_season, MAX(ep_episode) FROM Data
WHERE ep_season = (
SELECT MAX(ep_season)
FROM Data
WHERE u_ID = 1
AND s_imdbID = "tt4158110"
)
AND s_imdbID = "tt4158110"
AND u_ID = 1;


How can I integrate both of them into one SQL statement to get the following


seriesID | Max_Season | Max_Episode
-----------| ------------------| -----------
Value.... | Value............ | Value


this is the view code in the code you will be able to know the kind of data that will be retrieved from it

SELECT
-- episode data
e.title AS "ep_title",
e.year AS "ep_year",
e.rated AS "ep_rated",
e.released AS "ep_released",
e.season AS "ep_season",
e.episode AS "ep_episode",
e.runtime AS "ep_runtime",
e.genre AS "ep_genre",
e.director AS "ep_director",
e.writer AS "ep_writer",
e.actors AS "ep_actors",
e.plot AS "ep_plot",
e.language AS "ep_language",
e.country AS "ep_country",
e.awards AS "ep_awards",
e.poster AS "ep_poster",
e.metascore AS "ep_metascore",
e.imdbRating AS "ep_imdbRating",
e.imdbVotes AS "ep_imdbVotes",
e.imdbID AS "ep_imdbID",
-- series data
s.title AS "s_title",
s.year AS "s_year",
s.rated AS "s_rated",
s.released AS "s_released",
s.runtime AS "s_runtime",
s.genre AS "s_genre",
s.director AS "s_director",
s.writer AS "s_writer",
s.actors AS "s_actors",
s.plot AS "s_plot",
s.language AS "s_language",
s.country AS "s_country",
s.awards AS "s_awards",
s.poster AS "s_poster",
s.metascore AS "s_metascore",
s.imdbRating AS "s_imdbRating",
s.imdbVotes AS "s_imdbVotes",
s.imdbID AS "s_imdbID",
-- user data
u.ID AS "u_ID"/*, */
/*
u.username AS "u_username",
u.firstname AS "u_firstname",
u.lastname AS "u_lastname",
u.password AS "u_password",
u.email AS "u_email",
u.emailVerificationCode AS "u_emailVerificationCode",
u.location AS "u_location",
u.accesslevel AS "u_accesslevel",
u.disabled AS "u_disabled",
u.active AS "u_active"
*/
FROM test w
INNER JOIN users u
ON u.ID = w.userid
INNER JOIN episode e
ON w.epid = e.imdbID
INNER JOIN series s
ON e.seriesID = s.imdbID
WHERE e.seriesID IN (SELECT (imdbID) FROM series);


EDIT 1:

it's a series watchlist and the view Data are coming from a table in which I'm saving each user with it's watched episode

through that view I get the data of the episode, series, user

Answer

You can try using NOT EXISTS() :

SELECT * FROM Data t
WHERE NOT EXISTS(SELECT 1 FROM Data s
                 WHERE t.s_imdbID = s.s_imdbID
                 AND s.ep_season > t.ep_season)
 AND NOT EXISTS(SELECT 1 FROM Data p
                WHERE p.imdbID = t.imdbID AND p.ep_season = t.ep_season
                  AND p.ep_episode > t.ep_episode )

First one makes sure that no newer season exists, second one that no newer episode exists .

Comments