CODEWITHSUNDEEP
sqlsql-servert-sqlleft-joincorrelated-subquery
aprkturk
aprkturk

Reputation: 85

How to find the max value without subquery

To get the below result set I wrote following SQL:

Result Set

SELECT  t1.FilmName,
        t2.CountryName,
        t1.FilmRunTimeMinutes
FROM Film as t1
INNER JOIN country as t2 on t1.FilmCountryId = t2.CountryID
WHERE t1.FilmRunTimeMinutes = ( SELECT max(t2.FilmRunTimeMinutes) 
                                FROM film as t2 
                                WHERE t2.FilmCountryId = t1.FilmCountryId 
                              )
ORDER BY FilmRunTimeMinutes DESC

I read this Link and tried the same method but I could not. So how can I get the same result set using by LEFT OUTER JOIN?

Film table has those columns:

FilmId --PK
FilmName 
FilmCountryId --FK
FilmRunTimeMinutes

Country table has those columns:

CountryId --PK
CountryName

Thanks in advance.

Upvotes: 4

Views: 4319

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93754

use Row_Number window function 

SELECT TOP 1 WITH ties t1.FilmName,
                       t2.CountryName,
                       t1.FilmRunTimeMinutes
FROM   Film AS t1
       INNER JOIN country AS t2
               ON t1.FilmCountryId = t2.CountryID
ORDER  BY Row_number() OVER(partition BY FilmCountryId ORDER BY FilmRunTimeMinutes DESC),
          FilmRunTimeMinutes DESC;

or use CTE/Sub-Select

WITH cte
     AS (SELECT t1.FilmName,
                t2.CountryName,
                t1.FilmRunTimeMinutes,
                Rn = Row_number() OVER(partition BY FilmCountryId ORDER BY FilmRunTimeMinutes DESC)
         FROM   Film AS t1
                INNER JOIN country AS t2
                        ON t1.FilmCountryId = t2.CountryID)
SELECT *
FROM   cte
WHERE  Rn = 1
ORDER  BY FilmRunTimeMinutes DESC

if you really want left join approach then 

SELECT t1.FilmName,
       t2.CountryName,
       t1.FilmRunTimeMinutes
FROM   Film AS t1
       INNER JOIN country AS t2
               ON t1.FilmCountryId = t2.CountryID
       LEFT JOIN Film AS t3
              ON t3.FilmCountryId = t2.CountryID
                 AND t3.FilmRunTimeMinutes > t1.FilmRunTimeMinutes
WHERE  t3.FilmID IS NULL
ORDER  BY FilmRunTimeMinutes DESC

Upvotes: 6

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8043

Try This

;WITH Q
AS
(
    SELECT
        RN = ROW_NUMBER() OVER(PARTITION BY t1.FilmCountryId ORDER BY t2.FilmRunTimeMinutes DESC),
        t1.FilmName,
        t2.CountryName,
        t1.FilmRunTimeMinutes
    FROM Film as t1
    INNER JOIN country as t2 on t1.FilmCountryId=t2.CountryID
    ORDER BY FilmRunTimeMinutes DESC
)
SELECT
    *
    FROM Q
        WHERE RN = 1

Upvotes: -1

Related Questions