Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to get data from single column in pairs in ascending order

How to select data from single column in pairs from database?. Expected output is shown below as the id of employee1 is less than employee2. And these are grouped By Source "A". Thanks in advance.

Input: Employees Table

enter image description here

Expected Output Table:

enter image description here

I tried this

SELECT DISTINCT e1.employee_name AS employee1, e1.employee_name AS employee2, e2.employee_name
FROM Employees e1
LEFT JOIN Employees e2
ON e1.manager_id = e2.employee_id
GROUP BY e1.employee_name
ORDER BY e1.employee_id ASC;

But it gave me data in duplicates like below table which I don’t want.

employee1 employee2 employee_name Alice Alice NULL Bob Bob Alice Carol Carol Alice David David Bob Eve Eve Bob

Tags:

2

Answers


  1. 0

    Can you try this:

    first you need to exclude the item itself on join e1.id < e2.id
    second not to allow to have the same e1
    then you will see that e2 is doubled again this is why i used

    mod () 2 = 0

    There you have a working example

    CREATE TABLE `test_table` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR(256) NOT NULL , `source` VARCHAR(1) NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;

INSERT INTO `test_table` (`id`, `name`, `source`) VALUES (NULL, 'Test name 2', 'A');
INSERT INTO `test_table` (`id`, `name`, `source`) VALUES (NULL, 'Test name 3', 'B');
INSERT INTO `test_table` (`id`, `name`, `source`) VALUES (NULL, 'Test name 4', 'C');
INSERT INTO `test_table` (`id`, `name`, `source`) VALUES (NULL, 'Test name 5', 'A');
INSERT INTO `test_table` (`id`, `name`, `source`) VALUES (NULL, 'Test name 6', 'E');
INSERT INTO `test_table` (`id`, `name`, `source`) VALUES (NULL, 'Test name 7', 'A');
INSERT INTO `test_table` (`id`, `name`, `source`) VALUES (NULL, 'Test name 8', 'A');
INSERT INTO `test_table` (`id`, `name`, `source`) VALUES (NULL, 'Test name 9', 'A');
INSERT INTO `test_table` (`id`, `name`, `source`) VALUES (NULL, 'Test name 10', 'A');

SELECT 
    e1.id AS employee1_id,
    e1.name AS employee1_name,
    e2.id AS employee2_id,
    e2.name AS employee2_name,
    e1.source
FROM 
    test_table e1
JOIN 
    test_table e2
ON 
    e1.id < e2.id AND e1.source = e2.source
WHERE 
     NOT EXISTS (
        SELECT 1 
        FROM test_table e3 
        WHERE e3.id < e2.id AND e3.id > e1.id AND e3.source = e1.source
    )
    AND 0 = (
        SELECT count(1) as total
        FROM test_table e4 
        WHERE e4.id < e1.id and e4.source = e1.source
    ) MOD 2;
    Login or Signup to reply.
  2. 0

    First partition data with respect to source and then create row_number for every partition.

    Then separate data on odd & even row_number and join them with respect to same source and odd_table_rowNo = even_table_rowNo -1

    So, your data should be come as:
    enter image description here

    As, you filter out only Source: A data, so, need to add condition for that.

    Finally, MySQL query should be:

    WITH T as (
  select *, 
   ROW_NUMBER() over (partition by source order by id) as rownum
from employeeTable
order by source, rownum
)
select T1.name as employee1,
      T2.name as employee2,
      T1.source as Source
  from (select * from T where (rownum % 2 = 1)) T1
  left join (select * from T where (rownum % 2 = 0)) T2
  on T1.source = T2.source 
  and (T1.rownum + 1) = T2.rownum
-- condition added according to question expected output
where T1.source in ('A')

    Output:

    enter image description here

    Sample Code: db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search