How to delete all duplicates in one column and return only those that has date in next column in SQL-CodePudding

I have a table with 4 million rows and 2 columns: 1. phone number (account) and last active time (last_active). However, not all accounts has last_active date and have null instead. So, what I need is:

Firstly, delete all duplicates from account column and return the rest (I need only distinct phone numbers).

Secondly, while returning all distinct phone numbers (account), return only those which has a date (not null).

enter image description here

CodePudding user response：

Seems to be pretty straight forward select query with aggregation. What have you tried and what error or problem are you getting

SELECT Account, max(last_active) as Last_active
FROM TableName
WHERE last_active is not null
GROUP BY Account

maybe you don't want the most recent active however...

so maybe: but this would show duplicate accoutns with all "Last_active" dates recorded...

SELECT Account, last_active
FROM TableName
WHERE last_active is not null
GROUP BY Account

CodePudding user response：

Hi I've just made a sample table in MySQL with about 200,000 records and half of them are duplicates.

The following query works quite well.

SELECT * FROM TableName
WHERE last_active IS NOT NULL
AND 1 GROUP BY account

now if you want to save it a new table you could

CREATE TABLE newTableName as
SELECT * FROM TableName
WHERE last_active IS NOT NULL
AND 1 GROUP BY account

CodePudding user response：

I would agree with xQbert however I would add in a DISTINCT after the SELECT just in case there are times that account was active twice in the same date (If this is how the column is formatted) and both are recorded

SELECT DISTINCT Account, max(last_active) as Last_active
FROM TableName
WHERE last_active is not null
GROUP BY Account