Selecting the MAX Count based on a few criterias-CodePudding

I have an SQL query that groups the following fields and sums the Draw field

2021-08-01 |0010 |0105003 |10467 |BRONX |NY |15

2021-08-01 |0010 |0105003 |10469 |BRONX |NY |60

2021-08-02 |0010 |0105003 |10467 |BRONX |NY |14

2021-08-02 |0010 |0105003 |10469 |BRONX |NY |62

How do I tell SQL to give me the row with the largest draw for a particular date and particular route.

What I am trying to accomplish is to get the 'dominant' zip and the criteria for a dominant zip is the zip with the highest draw for the same date and route.

Results should be

2021-08-01 |0010 |0105003 |10469 |BRONX |NY |60

2021-08-02 |0010 |0105003 |10469 |BRONX |NY |62

Any help would be appreciated.

CodePudding user response：

Use cross-apply to join an aggregate.

declare @data table (DelDate datetime, Plant  int, Route int, Zip int, City varchar(50), State varchar(2), Draw int)
insert into @data values (cast('2021-08-01' as datetime), 0010, 0105003, 10467, 'BRONX','NY',15)
insert into @data values (cast('2021-08-01' as datetime), 0010, 0105003, 10469, 'BRONX','NY',60)
insert into @data values (cast('2021-08-02' as datetime), 0010, 0105003, 10467, 'BRONX','NY',14)
insert into @data values (cast('2021-08-02' as datetime), 0010, 0105003, 10469, 'BRONX','NY',62)
select d1.* from @data d1 cross apply (
select top 1 zip, max(draw) as max from @data group by zip order by 2 desc) d2
where d1.zip = d2.zip