Table name couponDetail.
Current Values in `couponDetail` table;
Here couponCode is unique field with status Active.
Means its not possible for two couponCode to exists with same
name and 'Active' status`enter code here.
-----------------------------------------------
|id | couponCode | status | Date | offer |
|-----------------------------------------------
|1 | First | Inactive| 3rd jan | 20% off|
|2 | Second | Inactive| 4th jan | 30% off|
|3 | First | Inactive| 5th jan | 10% off|
|4 | Second | Inactive| 5th jan | 40% off|
|5 | Second | Active | 6th jan | 50% off|
|6 | First | Active | 7th jan | 15% off|
------------------------------------------------
Desired output :-
-----------------------------------------------
|id | couponCode | status | Date | offer |
|--------------------------------------|---------
|5 | Second | Active | 3rd jan | 50% off|
|6 | First | Active | 4th jan | 15% off|
------------------------------------------------
Need only date value from the old row(If exists otherwise fetch the date which is in that new row) other values are needed to be fetched from the newly created rows.
I have to do this task using query only no stored procedures
CodePudding user response：
You appear to want most recent paired with first.
DROP TABLE IF EXISTS T;
CREATE TABLE T
(id INT, couponCode VARCHAR(20), status VARCHAR(20), Date VARCHAR(20), offer VARCHAR(20));
INSERT INTO T VALUES
(1 , 'First' , 'Inactive' , '3rd jan' , '20% off'),
(2 , 'Second' , 'Inactive' , '4th jan' , '30% off'),
(3 , 'First' , 'Inactive' , '5th jan' , '10% off'),
(4 , 'Second' , 'Inactive' , '5th jan' , '40% off'),
(5 , 'Second' , 'Active' , '6th jan' , '50% off'),
(6 , 'First' , 'Active' , '7th jan' , '15% off');
SELECT T.*,
(SELECT DATE FROM T T1 WHERE T1.COUPONCODE = T.COUPONCODE ORDER BY ID LIMIT 1) DT
FROM T
WHERE ID = (SELECT MAX(ID) FROM T T1 WHERE T1.COUPONCODE = T.COUPONCODE);
------ ------------ -------- --------- --------- ---------
| id | couponCode | status | Date | offer | DT |
------ ------------ -------- --------- --------- ---------
| 5 | Second | Active | 6th jan | 50% off | 4th jan |
| 6 | First | Active | 7th jan | 15% off | 3rd jan |
------ ------------ -------- --------- --------- ---------
2 rows in set (0.002 sec)
CodePudding user response：
You can use the min window function on the Date column:
with u as
(select id,
couponCode,
status, min(`Date`) over(partition by couponCode) as `Date`,
offer
from couponDetail)
select * from u where status = 'Active';