How to simplify SQL code for "MAX" operator-CodePudding

I was trying to solve one task and found the solution, it works, but the code is "awful" and duplicates itself, may be someone can just help me with that?

SELECT  b."N",b."ID",max(b.summ) as "jjj", b.country

FROM(SELECT großhandelsfirma.name as "N",großhandelsfirma.steuerid as "ID",
sum(verkauft.anzahl) as "summ", großhandelsfirma.land as "country"
FROM großhandelsfirma
INNER JOIN verkauft on großhandelsfirma.name = verkauft.ghname
GROUP BY großhandelsfirma.name,großhandelsfirma.steuerid,großhandelsfirma.land) b
WHERE b.summ in (SELECT  max(b.summ)

FROM(SELECT großhandelsfirma.name as "N",großhandelsfirma.steuerid as "ID",
sum(verkauft.anzahl) as "summ", großhandelsfirma.land as "country"
FROM großhandelsfirma
INNER JOIN verkauft on großhandelsfirma.name = verkauft.ghname
GROUP BY großhandelsfirma.name,großhandelsfirma.steuerid,großhandelsfirma.land) b
GROUP BY b.country)
GROUP BY b."N",b."ID", b.country

Original code was ok, but gave me solution with not needed lines, the main task was to find company with highest "sum" in each country, but I got some duplicates with countries, so answer included 2-3 best "summs" in 1 country, but I needed only 1.

This was the old code:

SELECT großhandelsfirma.name as N, großhandelsfirma.steuerid as ID,
sum verkauft.anzahl as summ, großhandelsfirm.land as country
FROM großhandelsfirma
INNER JOIN verkauft on großhandelsfirma.name = verkauft.ghname
GROUP BY großhandelsfirma.name, großhandelsfirma.steuerid, großhandelsfirma.land

CodePudding user response：

Perhaps the window function ROW_NUMBER has been implemented in your RDBMS.
It can be used to calculate a number based on an order.
So it'll be 1 for the top 1.

SELECT N, ID, country, summ
FROM
(
  SELECT f.name as N, f.steuerid as ID, f.land as country
  , SUM(v.anzahl) as summ
  , ROW_NUMBER() OVER (PARTITION BY f.land ORDER BY SUM(v.anzahl) DESC) as RN
  FROM großhandelsfirma f
  JOIN verkauft v ON f.name = v.ghname
  GROUP BY f.name, f.steuerid, f.land
) q
WHERE RN = 1

CodePudding user response：

Did you try doing the same with CTE? Please have a look into below link.

CTE

Or else use the TOP 1 from the result filtered.