What will be the request for these records?

Question

Entry 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 = area
• 3 = subregion
• 4 = country

I need to output records, there will be two columns, one of which is CHILD the other - MAX_Salein the column there MAX_Salecan be only the maximum amount of sales among the brothers and sisters of the corresponding child data.

Example:

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

Because Australia and Pakistan belong to the same parents, and Australia has the maximum cost of sales

Answer 1

You can use the window ( row_number) function 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

... if your table is called t. Replace if necessary.

:

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

.

, , :

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

:

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

first_value , " ": over .

Answer 2

, , , :

SELECT
Child,
MAx_Sales
FROM table1 inner join
(
  SELECT
  Parent,
  max(Sales) as Max_sales
  FROM  table1
  GROUP BY Parent 
) as table2
on table1.Parent = table2.Parent

Answer 3

I think the following code will work for you:

Select child, Sales as Max_Sales 
from table A
where Sales >= All (Select Sales from table B
                     Where A.level=B.Level
                       and A.Parent=B.Parent)

This is according to the understanding below. Let's say SubRegion A as 4 subsidiary countries p, q, r, s, then we need the name and sales of the country among the four with the maximum sales.