CODEWITHSUNDEEP
mysql
CrazyWooki
CrazyWooki

Reputation: 43

How do I get the fullest data record from a data set in mysql?

I'm trying to return a set of records that contain the most amount of data in the fields specified The primary key for the table is a license key so a user can register multiple licenses - there is no need for a user account, so we can't have a one to many relationship of a single user account to many licences (unfortunatley!)

Sometimes the user will give us a minimal amount of info. Eg firstname, lastname, email. Sometimes they give us more. a FacebookID and Date of Birth.

I've tried grouping on the email address and doing order by clauses on the FBID as in the query below, but I'm struggling with my knowledge of MySQL and I'm now wondering if it's even possible!?

SELECT firstname, lastname, email, FBID, DOB
FROM users
GROUP BY email
ORDER BY FBID IS NULL;

I want to extract the fullest amount of data from a set always grouping on email address:

John, Smith, [email protected], NULL, NULL
John, Smith, [email protected], 123456, NULL
John, Smith, [email protected], NULL, '1/1/1990'
Fred, Jones, [email protected], NULL, NULL

What I want to return:

John, Smith, [email protected], 123456, '1/1/1990'
Fred, Jones, [email protected], NULL, NULL

Thanks in advance!

Upvotes: 1

Views: 23

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133380

You could try using aggreagtion function for extact the values when are not null 

  select  firstname, lastname, email, max(FBID), max(DOB)
  from  users
  group by firstname, lastname, email
  order by  firstname, lastname, email

Upvotes: 1

Related Questions