digging for last joined records

[fulster]

Hi there,

Suppose I have two tables, customer and order.
for 1 customer, I can have 0 or more orders with details.

how can I manage to get, for each customer, his last order, (considering the date). Of course I wanna get some info from customer table and order table.

example

with :

client bilan
+----------+--------------+ +-------------------------+
| 1 | Mr Pipo | | 1 | 2003-01-02 |
+----------+--------------+ +-------------------------+
| 2 | Mme Mario | | 1 | 2003-01-30 |
+----------+--------------+ +-------------------------+
| 3 | Mme Andrée | | 2 | 2003-01-20 |
+----------+--------------+ +-------------------------+
| 4 | Mr Potrzebie| | 2 | 2003-05-23 |
+----------+--------------+ +-------------------------+
| 2 | 2003-01-03 |
+-------------------------+
| 3 | 2003-02-05 |
+-------------------------+
| 4 | 2003-06-12 |
+-------------------------+
| 4 | 2003-07-01 |
+-------------------------+

I should get :

+------------+--------------+----------------+
| 1 | Mr Pipo | 2003-01-30 |
+------------+--------------+----------------+
| 2 | Mme Mario | 2003-05-23 |
+------------+--------------+----------------+
| 3 | Mme Andrée | 2003-01-20 |
+------------+--------------+----------------+
| 4 | Mr Potrzebie| 2003-05-23 |
+------------+--------------+----------------+


is that possible in one shot ?

best regards !

 

[swampBoogie]

http://www.mysql.com/doc/en/example-Maximum-column-group-row.html

 

[fulster]

thanks a lot ... I could find the correct terms to search it !
the "one pass" solution is a little ugly for many columns.

maybe with subselect it will be easier

thanks again.