what will be the simple mysql query for this

I want to get the customer info where customer id should be 2 and the log_id should be maximum value i tried below query but it is fetching first record found.

What will be the sim开发者_开发百科ple query

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 suggest order by log_id desc I don't want to get in this way

---

     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

Edit without the Where:

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

---

Maybe this way:

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

A sub-select usually isn't bad as far as the execution plan is concerned.

---

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 damn...

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