SQL to map max and min in one row, next max and next min in the next row, and so on

Question

Here is an example:

Table

Name	Salary
A	1000
B	500
C	400
D	100

Output

Name	salary_highest	name	salary_Lowest
A	1000	D	100
B	500	C	400

So the highest and lowest salary should be mapped and come in a single row and the output should be a multiple rows not a single row that maps the high-low salaries until the table’s data ends.

I am able to get max and min, second max and second min and so on and only single row at a time but that is not what I want.

Any loops we can use. I am using MySQL - mycompiler.io online compiler.

Update: I tried this way -

Select E1.salary as MIN, E2.salary as MAX
from employees E1, employees E2 where
E1.salary < (select max(salary) from employees) and 
E2.salary < (select min(salary) from employees);

And got this output-

Output

MIN	MAX
500	1000
400	1000
100	1000
500	500
400	500
100	500
500	400
400	400
100	400

Guess the looping works but all the extra recs gotta go, output should be-

MIN	MAX
100	1000
400	500

which are present in the list. Where am I wrong here

What results if there are an odd number of rows? Ties in salaries? — ysth, Oct 22 '21 at 08:34
Consider adding better sample data and a [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example), as it stands there are too many ambiguities and scenarios not covered. You should also include your best attempt so far and where exactly you are stuck. — Stu, Oct 22 '21 at 08:45
If i understand properly, what you want to do is basicaly: get **both** `MAX(salary)` and `MIN(salary)` along with the proper `name`, and then rereun the same query and excluding the previous results ? — Hollyol, Oct 22 '21 at 08:57
Enimerate rows asc and desc then combine them (join 2 copies) into pairs by t1.n_asc+t2.n_desc=count+1. — Akina, Oct 22 '21 at 09:48

ysth · Answer 1 · 2021-10-22T14:30:41.893

You can calculate both ascending and descending rank in a common table expression, and then self-join it:

with ranked_employees as (
    select name, salary,
        row_number() over w_asc row_asc,
        row_number() over w_desc row_desc
    from employees
    window
        w_asc as (order by salary, id),
        w_desc as (order by salary desc, id desc)
)
select d.name, d.salary, a.name, a.salary
from ranked_employees d
inner join ranked_employees a on a.row_asc=d.row_desc and d.row_asc >= d.row_desc

The d.row_asc >= d.row_desc condition makes it stop at the midpoint.

Note that you need to order on id or some other unique field as well as salary to guarantee consistent ordering between the ascending and descending counts.

Hollyol · Answer 2 · 2021-10-22T13:24:10.780

Fast answer:

SELECT
  tdesc.name AS highname,
  tdesc.salary AS hightsalary,
  tasc.name AS lowname,
  tasc.salary AS lowsalary
FROM (
  SELECT
    ROW_NUMBER() OVER (ORBER BY salary, name) rank1,
    name,
    salary
  FROM table
  ORDER BY salary, name ASC
) AS tasc
INNER JOIN (
  SELECT
    ROW_NUMBER() OVER (ORDER BY salary, name) rank2,
    name,
    salary
  FROM table
  ORDER BY salary, name DESC
) AS tdesc
ON tdesc.rank2 = tasc.rank1
WHERE tdesc.salary >= tasc.salary

Explanation:

As @akina commented, we want to join to copies of the base table in reverse order:

~~To achieve this, we will first declare 2 row counters: rank1 and rank2.~~

EDIT: MySQL supports the ROW_NUMBER function that allows the same purpose.

The first subquery creates a temporary table tasc containing all values from the base table along a "order id", ordered by salary ASC.

tasc:

rank1	name	salary
1	A	1000
2	B	500
3	C	400
4	D	100

The second sub query will do the same but in reverse order.

tdesc:

rank2	name	salary
1	D	100
2	C	400
3	B	500
4	A	1000

Now, we join our to tables on tasc.rank1 = tdesc.rank2 with some aliases.

joined table:

highname	highsalary	lowname	lowsalary
A	1000	D	100
B	500	C	400
C	400	B	500
D	100	A	1000

As you can see, all rows are now duplicated, the second half has also irrelevant values, we must filter it out whith the where condition WHERE tdesc.salary >= tasc.salary.

Final result:

highname	highsalary	lowname	lowsalary
A	1000	D	100
B	500	C	400

IN THE CASE OF ODD NUMBER OF ROWS:

The or equal in the >= where clause will make sure you won't loose data, and the last row of the result will contain twice the datas for the median salary.

highname	highsalary	lowname	lowsalary
A	1000	D	100
B	500	C	400
E	450	E	450

IN THE CASE OF SAME SALARY FOR MULTIPLE PEOPLE:

The or equal will once again make sure you won't loose any data. In addition, the ORDER BY salary, name will make sure you have different names in the rows:

highname	highsalary	lowname	lowsalary
A	1000	D	100
B	500	C	400
E	450	F	450

SQL to map max and min in one row, next max and next min in the next row, and so on

Table

Output

Output

2 Answers2