问SQL:根据第一列取两列的最大值

EN

SELECT
      ar3.trlr_id
    , ar3.areanum
    , ar3.con
FROM (
      SELECT
            ar2.trlr_id
          , ar2.areanum
          , ar2.con
          , ROW_NUMBER() OVER(PARTITION BY ar2.trlr_id ORDER BY ar2.areanum, ar2.con DESC) as rn
      FROM (
            SELECT
                  ar1.trlr_id
                , ar1.areanum
                , COUNT(ar1.areanum) con
            FROM (
                  SELECT
                        t.trlr_id
                      , pm.prtnum
                      , to_number(CASE
                              WHEN pm.prtfam LIKE 'WH%' THEN substr(pm.prtfam, 3, 1)
                              ELSE '6'
                        END) areanum
                  FROM rcvtrk rt
                     , trlr t
                     , rcvlin rl
                     , prtftp pf
                     , prtftp_dtl pfd
                     , prtmst pm
                  WHERE t.trlr_id = rt.trlr_id
                        AND t.trlr_stat NOT IN ('D', 'C')
                        AND rl.trknum = rt.trknum
                        AND rl.prtnum = pf.prtnum
                        AND pfd.prtnum = pf.prtnum
                        AND pfd.ftpcod = pf.ftpcod
                        AND pf.defftp_flg = '1'
                        AND pfd.uomcod = 'UL'
                        AND pfd.wh_id = 'CP01'
                        AND pf.wh_id = 'CP01'
                        AND pf.prtnum = pm.prtnum
                        AND t.yard_loc IS NOT NULL
                        AND t.trlr_cod = 'RCV'
                        AND t.yard_loc_wh_id = 'CP01'
                  GROUP BY
                        t.trlr_id
                      , pm.prtnum
                      , pm.prtfam
                  ) ar1
            GROUP BY
                  ar1.trlr_id
                , ar1.areanum
            ) ar2
      ) ar3
WHERE ar3.rn = 1
;

SELECT
      *
FROM rcvtrk rt
      INNER JOIN trlr t ON rt.trlr_id = t.trlr_id
      INNER JOIN rcvlin rl ON rt.trknum = rl.trknum
      INNER JOIN prtftp pf ON rl.prtnum = pf.prtnum
      INNER JOIN prtftp_dtl pfd ON pf.prtnum = pfd.prtnum
                  AND pf.ftpcod = pfd.ftpcod
      INNER JOIN prtmst pm ON pf.prtnum = pm.prtnum
WHERE t.trlr_stat NOT IN ('D', 'C')
      AND t.yard_loc IS NOT NULL
      AND t.trlr_cod = 'RCV'
      AND t.yard_loc_wh_id = 'CP01'
      AND pf.defftp_flg = '1'
      AND pf.wh_id = 'CP01'
      AND pfd.uomcod = 'UL'
      AND pfd.wh_id = 'CP01'

SELECT Warehouse.id, WH, Count
FROM dbo.Warehouse as Warehouse
INNER JOIN
    (SELECT id, MAX(Count) AS MaxCount
    FROM dbo.Warehouse
    GROUP BY id) as groupedWH 
ON Warehouse.id = groupedWH.id
AND Count = groupedWH.MaxCount