GeoMaps GeoMaps - 10 months ago 158
SQL Question

How to insert MAX function in a subquery? (SQL Server)

I have the following SQL code that's shows me the number of selling per seller per sector.

SELECT
[JO_Info].Name, [Proc].Sector, COUNT([JO].JO_ID) AS total
FROM
[JA]
INNER JOIN
[Proc] ON [JA].ProcessID = [Proc].ProcessID
INNER JOIN
[JO] ON [JA].JO_ID = [JO].JO_ID
INNER JOIN
[JO_Info] ON [JO].JOI_ID = [JO_Info].JOI_ID
WHERE
([JO].JO_ID = 'seller')
GROUP BY
[JO_Info].Name, [Proc].Sector


The output is the following:

Name | Sector | total
-----------+---------+----------
Grace | 1 | 4
Elizabeth | 2 | 1
Bette | 3 | 3
Angelina | 4 | 6
Karl | 4 | 1
Alberto | 13 | 1
Jorge | 15 | 1


But I want the seller that sold more per sector. So, my question is how to insert the MAX function in order to get the following result (No repeats in the sector).

Name | Sector | total
-----------+---------+----------
Grace | 1 | 4
Elizabeth | 2 | 1
Bette | 3 | 3
Angelina | 4 | 6
Alberto | 13 | 1
Jorge | 15 | 1

Answer Source

You can use row_number():

with t as (<your query here>)
select t.*
from (select t.*, row_number() over (partition by sector order by total desc) as seqnum
      from t
     ) t
where seqnum = 1;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download