CODEWITHSUNDEEP
mysql
Ishpreet
Ishpreet

Reputation: 667

Get Last non-null cell in a row mysql

I have a table in following structure:

id       performance_id     employee_comments   manager_comments
1        23                 NULL                Well done.
2        46                 NULL                Improve Speed
3        46                 Trying to improve   NULL
4        46                 NULL                Put more effort

In above structure, at a time, only one comments exist, either employee_comments or manager_comments, not both simultaneously.

I want to fetch last non-empty employee comments and manager_comments in a single row.

I need mysql query to display output as:

performance_id     employee_comments   manager_comments
23                 NULL                Well done.
46                 Trying to improve   Put more effort

Upvotes: 2

Views: 1005

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562398

You can get the largest id for each of the employee comments and manager comments, per performance_id this way:

SELECT performance_id, 
  MAX(CASE WHEN employee_comments IS NOT NULL THEN id END) AS emp_id,
  MAX(CASE WHEN manager_comments IS NOT NULL THEN id END) AS mgr_id
FROM MyTable
GROUP BY performance_id

The above only returns one row per performance_id, with two id numbers. These are the "last" entries, as determined by greater id numbers.

Output:

+----------------+--------+--------+
| performance_id | emp_id | mgr_id |
+----------------+--------+--------+
|             23 |   NULL |      1 |
|             46 |      3 |      4 |
+----------------+--------+--------+

With that result, you can join it back to the table to retrieve other columns:

SELECT t.performance_id, 
    t.employee_comments,
    t.manager_comments
FROM (
    SELECT performance_id, 
      MAX(CASE WHEN employee_comments IS NOT NULL THEN id END) AS emp_id,
      MAX(CASE WHEN manager_comments IS NOT NULL THEN id END) AS mgr_id
    FROM MyTable
    GROUP BY performance_id
) AS x
JOIN MyTable AS t ON t.id IN (emp_id, mgr_id)

Output:

+----------------+-------------------+------------------+
| performance_id | employee_comments | manager_comments |
+----------------+-------------------+------------------+
|             23 | NULL              | Well done.       |
|             46 | Trying to improve | NULL             |
|             46 | NULL              | Put more effort  |
+----------------+-------------------+------------------+

The above return up to two rows per performance_id. You can use another GROUP BY to force them onto one row:

SELECT t.performance_id, 
    MAX(t.employee_comments) AS employee_comments,
    MAX(t.manager_comments) AS manager_comments
FROM (
    SELECT performance_id, 
      MAX(CASE WHEN employee_comments IS NOT NULL THEN id END) AS emp_id,
      MAX(CASE WHEN manager_comments IS NOT NULL THEN id END) AS mgr_id
    FROM MyTable
    GROUP BY performance_id
) AS x
JOIN MyTable AS t ON t.id IN (emp_id, mgr_id)
GROUP BY performance_id

Output:

+----------------+-------------------+------------------+
| performance_id | employee_comments | manager_comments |
+----------------+-------------------+------------------+
|             23 | NULL              | Well done.       |
|             46 | Trying to improve | Put more effort  |
+----------------+-------------------+------------------+

Upvotes: 2

indago
indago

Reputation: 2101

This can be done by a nested query in MySQL. We will have to do a union and group by performance_id

 SELECT performance_id ,MAX(employee_comments) 
employee_comments,MAX(manager_comments) manager_comments 
FROM(
SELECT performance_id,employee_comments,manager_comments FROM MyTable 
    WHERE id IN (SELECT * FROM 
            (SELECT id FROM MyTable WHERE manager_comments IS NOT NULL ORDER BY id DESC)AS t)
    UNION
    SELECT performance_id,employee_comments,manager_comments FROM MyTable 
    WHERE id IN (SELECT * FROM 
            (SELECT id FROM MyTable WHERE employee_comments IS NOT NULL ORDER BY id DESC) AS p)) g  GROUP BY performance_id;

The above query should work for fetching the last non-empty employee comments and manager_comments I have tried and tested it...

Upvotes: 0

Bhargav Chudasama
Bhargav Chudasama

Reputation: 7171

try this query

SELECT t.id,t.performance_id,t2.employee_comments,t3.manager_comments FROM test1  t join test1 t2 on t2.employee_comments IS NOT NULL and t.manager_comments IS NULL
join test1 t3 on t3.manager_comments IS NOT NULL and t.id < t3.id 
group by t.performance_id

Upvotes: 0

Related Questions