zdnPXbUV

1SELECT [supplier town], [product id], [quantity]
2FROM my_cube
3WHERE "supplier town" is not null
4AND "supplier name" is not null
5GROUP BY [supplier town], [product id], [quantity]
6HAVING [quantity] >= ALL(
7   SELECT [quantity]
8   FROM my_cube)
9	
10SELECT [supplier town], [product id], [quantity]
11FROM my_cube aa
12WHERE "supplier town" is not null
13--AND "supplier name" is not null
14GROUP BY [supplier town], [product id], [quantity]
15HAVING [quantity] >= ALL(
16   SELECT [quantity]
17   FROM my_cube bb where aa.[supplier town] = bb.[supplier town])
18
19supplier town   product id  quantity
20Chicago         Computer    1010
21Madison         Gas         200000
22Springfield     Computer    100
23Stevens Point   Airplane    110000
24Stevens Point   Computer    110000
25Wausau          Boat        200100
26	
27drop table if exists #data;
28
29create table #data(
30    city nvarchar(100) ,
31    product nvarchar(100) ,
32    quantity int
33);
34
35insert  #data
36values  ('Chicago', 'Airplane', 1000) ,
37        ('Chicago', 'Boat', 10) ,
38        ('Chicago', 'Computer', 1010) ,
39        ('Stevens Point', 'Computer', 100) ,
40        ('Stevens Point', 'Airplane', 110000) ,
41        ('Stevens Point', 'Computer', 110000)
42;
43
44
45select  city ,
46        product ,
47        quantity ,
48        row_number() over (partition by city order by quantity desc, product desc) as [order]
49from    #data
50	
51select  *
52from    (
53    select  city ,
54            product ,
55            quantity ,
56            row_number() over (partition by city order by quantity desc, product desc) as [order]
57    from    #data
58) x
59where   x.[order] = 1
60	
61with
62    x as (
63        select  city ,
64                product ,
65                quantity ,
66                row_number() over (partition by city order by quantity desc, product desc) as [order]
67        from    #data
68    )
69select * from x where [order] = 1