4

I have a temporary table I've derived from a much larger table.

+-----+----------+---------+
| id  | phone    | attempt |
+-----+----------+---------+
|  1  | 12345678 |      15 |
|  2  | 87654321 |       0 |
|  4  | 12345678 |      16 |
|  5  | 12345678 |      14 |
|  10 | 87654321 |       1 |
|  11 | 87654321 |       2 |
+-----+----------+---------+

I need to find the id (unique) corresponding to the highest attempt made on each phone number. Phone and attempt are not unique.

SELECT id, MAX(attempt) FROM temp2 GROUP BY phone

The above query does not return the id for the corresponding max attempt.

PrashanD
  • 2,643
  • 4
  • 28
  • 58

2 Answers2

3

Try this:

select
    t.*
from temp2 t
inner join (
    select phone, max(attempt) attempt
    from temp2
    group by phone
) t2 on t.phone = t2.phone
and t.attempt = t2.attempt;

It will return rows with max attempts for a given number.

Note that this will return multiple ids if there are multiple rows for a phone if the attempts are same as maximum attempts for that phone.

Demo here

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
  • Says ERROR 1137 (HY000): Can't reopen table: 't'. I ran into this problem earlier as well. Turns out it's because this is temporary table – PrashanD Jan 27 '17 at 03:44
  • [You can't access the temp table more than once](http://stackoverflow.com/questions/343402/getting-around-mysql-cant-reopen-table-error). Don't use temp table, create view or a different table instead. The above and @Tim's solution is the way to solve this problem. – Gurwinder Singh Jan 27 '17 at 03:46
  • @Tim - Thanks for the fiddle :) – Gurwinder Singh Jan 27 '17 at 03:54
  • I went with creating another temporary table. select t.* from temp2 t inner join ( select msisdn, max(attempt) attempt from temp3 group by phone ) t2 on t.phone = t2.phone and t.attempt = t2.attempt; – PrashanD Jan 27 '17 at 04:14
  • @Prashan - Nice one. Was about to suggest that. :) – Gurwinder Singh Jan 27 '17 at 04:17
2

As an alternative to the answer given by @GurV, you could also solve this using a correlated subquery:

SELECT t1.*
FROM temp2 t1
WHERE t1.attempt = (SELECT MAX(t2.attempt) FROM temp2 t2 WHERE t2.phone = t1.phone)

This has the advantage of being a bit less verbose. But I would probably go with the join option because it will scale better for large data sets.

Demo

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360