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

How to get multiple rows data in two different column of same id in Mysql

I have one table and data in it. Below is the table

+----+-------+------+---------+-------------+-----------+
| Id | p_name| grade| promoted| created_on  | review_by |
+----+-------+------+---------+-------------+-----------+
| 1  | Abc   | A    |   Yes   | 2023-Jun-14 |    AK     |
| 1  | Abc   | A    |   Yes   | 2023-Jun-17 |    RSK    |
| 1  | Abc   | B    |   Yes   | 2023-Jun-15 |    PS     |
| 1  | Abc   | B    |   Yes   | 2023-Jun-16 |    ZX     |
| 2  | Pqr   | A    |   Yes   | 2023-May-10 |    CB     |
| 2  | Pqr   | B-   |   Yes   | 2023-May-05 |    MN     |
| 2  | Pqr   | B    |   Yes   | 2023-May-07 |    KL     |
+----+-------+------+---------+-------------+-----------+

I need below result and it is based on conditions

+----+-------+--------------+-------------------+--------------+------------------+
| Id | p_name| Date for AA  | review_by for AA  | Date for BB  | review_by for BB |
+----+-------+--------------+-------------------+--------------+------------------+ 
| 1  |  Abc  | 2023-Jun-17  |    RSK            | 2023-Jun-16  |      ZX          | 
| 2  |  Pqr  | 2023-May-10  |    CB             | 2023-May-7   |      KL          | 
+----+-------+--------------+-------------------+--------------+------------------+

conditions : –

  1. When Grade in ( A,A-) and promoted = Yes then
    select Latest Date(‘created on’ column) save value into "Date for AA" column
    and (‘revew_by’ column as per latest date) save value into "review_by for AA" column

  2. When Grade in ( B,B-) and promoted = Yes then
    select Latest_Date(‘created_on’ column) save value into "Date for BB" column
    and (‘revew_by’ column as per latest date) save value into "review_by for BB" column

This condition is for each id(1,2) and get single result for each id as mention in
result table.

In details : – Latest Date When Grade  A/A- is promoted to Yes in "Date for AA"
column and reviewby as per latest date of "created_on" column and it should be group by id.
And Latest Date When Grade  B/B- is promoted to Yes in "Date for BB"
column and reviewby as per latest date of "created_on" column and it should be group by id

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    my old query is

    SELECT  Id, p_name,
(CASE WHEN (grade = 'A' OR grade = 'A-') AND promoted = 'Yes' THEN Max(created_on) END) AS 'Date for AA',
(CASE WHEN (grade = 'A' OR grade = 'A-') AND promoted = 'Yes' THEN review_by END) AS 'review_by for AA',
(CASE WHEN (grade = 'B' OR grade = 'B-') AND promoted = 'Yes' THEN Max(created_on) AS 'Date for BB',
(CASE WHEN (grade = 'B' OR grade = 'B-') AND promoted = 'Yes' THEN review_by END) AS 'review_by for BB'
FROM table_name
GROUP BY Id, p_name;

    After some little changes, i got my answer.Below is correct query

    SELECT Id, p_name,
MAX(CASE WHEN (grade = 'A' OR grade = 'A-') AND promoted = 'Yes' THEN created_on END) AS 'Date for AA',
MAX(CASE WHEN (grade = 'A' OR grade = 'A-') AND promoted = 'Yes' THEN review_by END) AS 'review_by for AA', 
MAX(CASE WHEN (grade = 'B' OR grade = 'B-') AND promoted = 'Yes' THEN created_on END) AS 'Date for BB',
MAX(CASE WHEN (grade = 'B' OR grade = 'B-') AND promoted = 'Yes' THEN review_by END) AS 'review_by for BB'
FROM table_name
GROUP BY Id, p_name

  2. 0

    Your query returns the incorrect value for review_by for BB for Id = 2. This is because you are returning MAX(review_by) per group, not review_by for MAX(created_on) per group.

    Assuming you are using MySQL >= 8.0, you can use ROW_NUMBER() in a CTE, and then your conditional aggregation to pivot the remaining rows:

    WITH max_t AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Id, LEFT(grade, 1) ORDER BY created_on DESC) AS rn
    FROM table_name
    WHERE promoted = 'Yes'
)
SELECT Id, p_name,
    MAX(CASE WHEN grade IN('A', 'A-') THEN created_on END) AS 'Date for AA',
    MAX(CASE WHEN grade IN('A', 'A-') THEN review_by END) AS 'review_by for AA', 
    MAX(CASE WHEN grade IN('B', 'B-') THEN created_on END) AS 'Date for BB',
    MAX(CASE WHEN grade IN('B', 'B-') THEN review_by END) AS 'review_by for BB'
FROM max_t
WHERE rn = 1
GROUP BY Id, p_name;

    For older versions of MySQL, you can use an aggregate query to get the max dates as a derived table and then join back to the main table:

    SELECT t.Id, t.p_name,
    MAX(CASE WHEN t.grade IN('A', 'A-') THEN t.created_on END) AS 'Date for AA',
    MAX(CASE WHEN t.grade IN('A', 'A-') THEN t.review_by END) AS 'review_by for AA', 
    MAX(CASE WHEN t.grade IN('B', 'B-') THEN t.created_on END) AS 'Date for BB',
    MAX(CASE WHEN t.grade IN('B', 'B-') THEN t.review_by END) AS 'review_by for BB'
FROM table_name t
JOIN (
    SELECT Id, LEFT(grade, 1) AS grade2, MAX(created_on) max_created_on
    FROM table_name
    WHERE promoted = 'Yes'
    GROUP BY Id, grade2
) t_max
    ON t.Id = t_max.Id
    AND LEFT(t.grade, 1) = t_max.grade2
    AND t.created_on = t_max.max_created_on
GROUP BY t.Id, t.p_name;

    Here’s a db<>fiddle.

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

Back To Top
Search