Dynamic mysql Select stament

Question

I have a table like:

+-----------------------------+
| ID | ID_CONV | NAME | DATA  |
| 1  |    1    | ALEX | DATA1 |
| 2  |    1    | ALEX | DATA2 |
| 3  |    2    | ALEX | DATA3 |
| 4  |    2    | ALEX | DATA4 |
| 5  |    3    | NIKO | DATA5 |
| 6  |    4    | ALEX | DATA6 |
| 7  |    4    | ALEX | DATA7 |
| 8  |    3    | NIKO | DATA8 |
+-----------------------------+

and I want to select the alex's DATA but only with the max ID_CONV, some like this:

+-----------------------------+
| ID | ID_CONV | NAME | DATA  |
| 6  |    4    | ALEX | DATA6 |
| 7  |    4    | ALEX | DATA7 |
+-----------------------------+

Currently, to do that, I have to ask for the max Alex's ID_CONV, and then with this ID_CONV select the data.

My question is if is possible make more efficient this consult

Answer 1

You can find max of id_conv for each name and join it with the main table like this:

SELECT t.*
FROM your_table t
INNER JOIN
  ( SELECT name, MAX(id_conv) id_conv FROM your_table GROUP BY name
  ) t2
ON t.name     = t2.name
AND t.id_conv = t2.id_conv
WHERE t.name  = 'ALEX';

Here is one with (a slower) correlated query:

SELECT *
FROM your_table t
WHERE t.name = 'ALEX'
AND id_conv IN
  ( SELECT MAX(id_conv) id_conv FROM your_table t2 WHERE t2.name = t.name
  ) ;

Answer 2

Try this:

SELECT ID , MAX(ID_CONV) , NAME , DATA  
  FROM MyTable
 WHERE NAME = 'ALEX'