What would be a simple mysql query for this

Question

I want to get information about the client, where the client ID should be 2, and log_id should be the maximum value. I tried the request below, but it retrieves the first record found.

What will be a simple request

mysql> select * from log_customer where customer_id =2 group by customer_id having max(log_id); +--------+------------+-------------+---------------------+-----------+----------+ | log_id | visitor_id | customer_id | login_at | logout_at | store_id | +--------+------------+-------------+---------------------+-----------+----------+ | 2 | 56 | 2 | 2010-02-19 19:34:45 | NULL | 1 | +--------+------------+-------------+---------------------+-----------+----------+ 1 row in set (0.00 sec) mysql> select * from log_customer where customer_id =2 limit 5; +--------+------------+-------------+---------------------+---------------------+----------+ | log_id | visitor_id | customer_id | login_at | logout_at | store_id | +--------+------------+-------------+---------------------+---------------------+----------+ | 2 | 56 | 2 | 2010-02-19 19:34:45 | NULL | 1 | | 3 | 114 | 2 | 2010-02-23 17:31:55 | NULL | 1 | | 31 | 1854 | 2 | 2010-03-08 18:31:28 | 2010-03-08 18:56:49 | 1 | | 32 | 1992 | 2 | 2010-03-09 01:12:43 | NULL | 1 | | 33 | 2304 | 2 | 2010-03-09 14:42:39 | NULL | 1 | +--------+------------+-------------+---------------------+---------------------+----------+ 

Please do not offer order by log_id desc I do not want to do this

Answer 1

  SELECT * FROM log_customer WHERE customer_id = 2 AND log_id = (Select Max(Log_id) FROM log_Customer WHERE customer_id = 2) 

That should do the trick

Change without "Where":

  SELECT * FROM log_customer WHERE log_id = (Select Max(Log_id) FROM log_Customer WHERE customer_id = 2) 

Answer 2

 select * from log_customer left join (select max(log_id) as max_id from log_customer where customer_id=2) as log_customer2 on log_customer.log_id=log_customer2.max_id where log_customer.customer_id=2; 

Oh shit...

 select * from log_customer where customer_id=2 order by log_id desc limit 1; 

Answer 3

Perhaps so:

 SELECT * FROM Log_Customer WHERE Customer_Id = 2 AND Log_Id = (SELECT Max(Log_Id) FROM Log_Customer WHERE Customer_Id = 2) 

Subexposure is usually not bad in terms of performance.