Ch6_1

Ch6_1.sql

WITH WT_M AS
  (SELECT FIRSTNAME,
    WEIGHT,
    ROW_NUMBER() over (order by WEIGHT DESC) AS WT_RANK_MALE
  FROM CHILDSTAT
  WHERE GENDER = 'm'
  ),
  WT_F AS
  (SELECT FIRSTNAME,
    WEIGHT,
    ROW_NUMBER() over (order by WEIGHT DESC) AS WT_RANK_FEMALE
  FROM CHILDSTAT
  WHERE GENDER = 'f'
  )
SELECT a.FIRSTNAME AS FIRSTNAME_MALE,
  a.WEIGHT         AS WEIGHT_MALE,
  B.FIRSTNAME      AS FIRSTNAME_FEMALE,
  B.WEIGHT         AS WEIGHT_FEMALE
FROM WT_M a
INNER JOIN WT_F B
ON A.WT_RANK_MALE = B.WT_RANK_FEMALE