CODEWITHSUNDEEP
mysqlsql
db100
db100

Reputation: 55

Mysql place data on multiple rows into one row based on date

I have the following data in a MYSQL table.

ID NAME PHONENO EMAIL TMDate
1234 Sarah 0884100300 1-Jan-21
1234 Sarah 0881500900 5-Mar-21
1234 Sarah [email protected] 10-Mar-21
0001 James 09991234567 31-Dec-20
0001 James [email protected] 31-Dec-20

The table contain email and phone numbers for customers. If the phonenumber or email is updated, the data is recorded on a new row. The TMDate column shows when the record was collected.

I want the final output to be as follows:

ID NAME PHONENO EMAIL
1234 Sarah 0881500900 [email protected]
0001 James 09991234567 [email protected]

My approach was to first pull the phone numbers based on max data using script below and then pull the emails using a script with similar logic and then do inner join of the two

 SELECT t1.id,
   t1.name
   t2.PHONENO,
   t2.max_date
 FROM phoneemail t1
  INNER JOIN
   (SELECT id,PHONENO, MAX(TMDATE) as max_date FROM phoneemail group by id,phoneno where t1.phoneno 
 is not null) t2
   ON t1.id = t2.id

My approach is not working as I am still getting duplicate records

Upvotes: 1

Views: 76

Answers (2)

forpas
forpas

Reputation: 164214

The columns PHONENO and EMAIL my have been updated in different dates, so getting the latest date for each ID will not help.

If your version of MySql is 8.0+ you can do it with FIRST_VALUE() window function, which does not support IGNORE NULLS, so conditional sorting is also needed:

SELECT DISTINCT ID, NAME,
       FIRST_VALUE(PHONENO) OVER (PARTITION BY ID, NAME ORDER BY PHONENO IS NULL, TMDate DESC) PHONENO,
       FIRST_VALUE(EMAIL) OVER (PARTITION BY ID, NAME ORDER BY EMAIL IS NULL, TMDate DESC) EMAIL
FROM phoneemail

For prior versions you need 2 correlated subqueries:

SELECT DISTINCT p1.ID, p1.NAME,
  (SELECT p2.PHONENO FROM phoneemail p2 
   WHERE (p2.ID, p2.NAME) = (p1.ID, p1.NAME) AND p2.PHONENO IS NOT NULL 
   ORDER BY TMDate DESC LIMIT 1) PHONENO,
  (SELECT p3.EMAIL FROM phoneemail p3 
   WHERE (p3.ID, p3.NAME) = (p1.ID, p1.NAME) AND p3.EMAIL IS NOT NULL 
   ORDER BY TMDate DESC LIMIT 1) EMAIL 
FROM phoneemail p1

See the demo.
Results:

ID NAME PHONENO EMAIL
1234 Sarah 0881500900 [email protected]
0001 James 09991234567 [email protected]

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

MySQL does not have a "first" or "last" aggregation function. So, one method is to use window functions:

select distinct id, name,
       first_value(phone ignore nulls) over (partition by id order by tmdate desc) as phone,
       first_value(email ignore nulls) over (partition by id order by tmdate desc) as email
from (select distinct id, name from t) t;

Happily, first_value() has the ignore nulls option.

Upvotes: 1

Related Questions