How to improve Using RANK() top 2 results into unique columns

Question

Here is the SQL 9(i) code written to show results 1 and 2 in their own columns. Is there a more efficient way to write this?

select 
  sc1.COIL as COIL1
, sc1.DEFECT as DEFECT1
, sc2.DEFECT as DEFECT2
FROM
      (select
            COIL, DEFECT
            , RANK() OVER(PARTITION BY COIL ORDER BY WEIGHT DESC) RNK
            from NOVELIS.F406, NOVELIS.F408 where   F406_DEFECT_CODE = F408_REJECT_CODE
            GROUP BY COIL, DEFECT
        )sc1
    , (select
            COIL, DEFECT
            , RANK() OVER(PARTITION BY COIL ORDER BY WEIGHT DESC) RNK
            from NOVELIS.F406, NOVELIS.F408 where   F406_DEFECT_CODE = F408_REJECT_CODE
            GROUP BY COIL, DEFECT
        )sc2

WHERE 
sc1.RNK = 1 
and sc2.RNK = 2 
and sc1.COIL = sc2.COIL 

Answer 1

You can use conditional aggregation and use 1 derived table

SELECT    sc1.COIL AS COIL1,
          MAX(CASE WHEN RNK = 1 THEN sc1.DEFECT END) AS DEFECT1,
          MAX(CASE WHEN RNK = 2 THEN sc1.DEFECT END) AS DEFECT2
FROM
(
    SELECT COIL,
           DEFECT,
           RANK() OVER(PARTITION BY COIL ORDER BY WEIGHT DESC) RNK
    FROM   NOVELIS.F406
           --practice using joins
           INNER JOIN NOVELIS.F408 ON F406_DEFECT_CODE = F408_REJECT_CODE
    --not sure you need the group by here
) sc1
GROUP BY sc1.COIL