Here is an example:

Table

Output

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

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

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

CodePudding user response：

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.

CodePudding user response：

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:

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

tdesc:

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

joined table:

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:

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.

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: