Mysql Retrieving Two Columns As One Distinct or Group By

Question

I am stumped with a mysql query. I am attempting to bring back all unique values from two columns sorted by date. Here is my table:

from                to               body      created_on
+55555555555        +22222222222     hello     2015-03-20 01:00:00
+11111111111        +33333333333     hello     2015-03-21 01:00:00
+33333333333        +44444444444     hello     2015-03-18 01:00:00
+66666666666        +11111111111     hello     2015-03-12 01:00:00
+11111111111        +77777777777     hello     2015-03-05 01:00:00

Basically it is a table that stores sms messages and I want to pull back the 100 most recent phone numbers that have entries into the sms table. So something like this:

number
+11111111111
+77777777777
+66666666666
+22222222222
+33333333333

Another requirement is that I don't pull bring back the phone numbers +4444444444 and +555555555555 because they are administrator numbers. The numbers should also be sorted by date.

Here is what I have so far:

SELECT `from` as num, created_at FROM sms WHERE `from` NOT IN (+4444444444, +5555555555)
GROUP BY num
UNION
SELECT `to` as num, created_at FROM sms WHERE `to` NOT IN (+4444444444, +5555555555)
GROUP BY num
ORDER BY created_at

I know I'm close but it's bringing back duplicates which I don't want.

Can you provide at least enough data to see the duplicates ? I've trying this and can't see any duplicates using you data — Loufylouf, Mar 19 '16 at 16:32
Duplicates will be present in his query, since they are Union of two not related result sets — Muhammed, Mar 19 '16 at 16:33
Check this out http://stackoverflow.com/questions/6744759/mysql-select-distinct-values-in-two-columns — Muhammed, Mar 19 '16 at 16:35

score 0 · Accepted Answer · answered Mar 19 '16 at 16:37

0

You can apply MAX initially in the two queries of the UNION operation so as to get the most recent date per number. The apply it once more on the whole set and order by date in descending order with a LIMIT clause to get the 100 most recent numbers:

SELECT num, MAX(created_at) AS created_at
FROM (
  SELECT `from` as num, MAX(created_at) AS created_at
  FROM sms 
  WHERE `from` NOT IN (+4444444444, +5555555555)
  GROUP BY num

  UNION

  SELECT `to` as num, MAX(created_at) AS created_at
  FROM sms 
  WHERE `to` NOT IN (+4444444444, +5555555555)
  GROUP BY num) AS t
GROUP BY number
ORDER BY created_at DESC LIMIT 100

answered Mar 19 '16 at 16:37

Giorgos Betsos

71,379
9
63
98

Yes, this worked. Interesting use of the subquery inside the FROM. I ran it on a table with 7000 rows with no limit and it only took 0.06s on my local machine. I also modified it slightly to have the admin numbers in a separate table `admin_number` and use another subquery so i switched `WHERE \`from\` NOT IN (+4444444444, +5555555555)` to `WHERE \`from\` NOT IN (SELECT admin_number.number FROM admin_number)` – WarrenR Mar 21 '16 at 16:44
@WarrenR Glad I was able to help and welcome to Stack Overflow. Please mark this or any other answer as accepted if it helped you solve your problem. – Giorgos Betsos Mar 21 '16 at 17:04

Mysql Retrieving Two Columns As One Distinct or Group By

1 Answers1