Zahid Zahid - 4 months ago 10
SQL Question

What will be the query to retrieve these records?

Input table: Sales Data

+---------------+---------------+---------- +-----------+
| Child | Parent | Level | Sales |
+---------------+---------------+---------- +-----------+
| All Region | All Region | 1 | 1000000 |
| Africa Region | All Region | 2 | 159816 |
| America | All Region | 2 | 114054 |
| Asia | All Region | 2 | 74028 |
| Europe Region | All Region | 2 | 116766 |
| Africa | Africa Region | 3 | 81954 |
| Asia Pacific | Asia | 3 | 144306 |
| Europe | Europe Region | 3 | 1440 |
| North America | America | 3 | 8185 |
| South America | America | 3 | 8440 |
| Argentina | South America | 4 | 470 |
| Australia | Asia Pacific | 4 | 9040 |
| Pakistan | Asia Pacific | 4 | 705 |
| South Africa | Africa | 4 | 45 |
| Tunisia | Africa | 4 | 385 |
| Uruguay | South America | 4 | 420 |
+-------------------------------------------------------+


Level


  • 1 = all regions

  • 2 = region

  • 3 = subregion

  • 4 = country



I need to output records there will be two columns one is CHILD another is
MAX_Sale
, in the
MAX_Sale
column only maximum sales amount will be there among the siblings of the corresponding child data.

Example:

+-----------+------------+
| Childs | Max_sales |
+-----------+------------+
| Austrlia | 9040 |
| Pakistan | 9040 |
| S.Africa | 385 |
| Tunisia | 385 |
+------------------------+


As Australia and Pakistan both belong to same parents and Australia has max sales value

Answer

You could use a window function (row_number) to get this result:

select child, MAX_Sale
from  (
        select child, 
               sales as MAX_Sale,
               row_number() over (partition by parent order by sales desc) as rn
        from   t
      ) sub
where rn = 1

... assuming your table is called t. Replace as needed.

Output is:

 Child         | MAX_Sale
---------------+---------
 Tunisia       |     385
 Africa        |   81954
 All Region    | 1000000
 South America |    8440
 Asia Pacific  |  144306
 Australia     |    9040
 Europe        |    1440
 Argentina     |     470

Depending on your needs, you may still need to filter by level, and apply a certain sort order.

Listing all regions

If however you need to list all regions, with the sales of the sibling with the best sales, then:

select child, 
       first_value(sales) over (partition by parent order by sales desc) as MAX_Sale
from   t

The output:

Child         | MAX_Sale
--------------+----------
Tunisia       |     385
South Africa  |     385
Africa        |   81954
All Region    | 1000000
Africa Region | 1000000
Europe Region | 1000000
America       | 1000000
Asia          | 1000000
South America |    8440
North America |    8440
Asia Pacific  |  144306
Australia     |    9040
Pakistan      |    9040
Europe        |    1440
Argentina     |     470
Uruguay       |     470
Comments