MySQL: Select penult values

There are 2 tables:

table1:

id  |phone| order|
 ---|-----|------|
  1 | 122 |  6   |
  2 | 122 |  4   |
  3 | 122 |  3   |
  4 | 123 |  6   |
  5 | 123 |  5   |
  6 | 123 |  3   |   
  7 | 124 |  6   |
  8 | 124 |  5   |
  9 | 125 |  6   |
  10| 125 |  5   |

table2:

|phone |
|------|
|122   |
|123   |
|124   |

I have to select id and last order according next conditions:

• If order not equals 3 take row with max id value for this phone
• If order equals 3 take pre-max id for this phone
• Id is in table2.

So result should be:

|phone  | order|
|------ |------|
|122    | 4    |
|123    | 5    |
|124    | 5    |

MySQL version: Ver 15.1 Distrib 5.5.64-MariaDB

Answer

Basically you want to look at the last two records; if the last record has order 3, then use the previous one.

That would have been a simple query with window functions and/or lateral joins be your old MySQL version does not support these features. User variables are an option, as demonstrated by nbk, but they are tricky to use – and MySQL 8.0 annonced that this feature will be deprecated in a future version.

I am going to recommend correlated subqueries and a little logic:

select t2.id,
    coalesce(
        nullif((select ord from table1 t1 where t1.id = t2.id order by odering_id desc limit 1), 3),
        (select ord from table1 t1 where t1.id = t2.id order by odering_id desc limit 1, 1)
    ) as ord
from table2 t2

The first subquery gets the latest value; nullif() checks the returned value and returns null if it has order 3; this indicate coalesce() that it should return the result of the second subquery, that gets the previous value.

order is a language keyword, so I used ord instead.

Demo in MySQL 5.5:

 id | ord
--: | --:
122 |   4
123 |   5
124 |   5