Hi René,
thsi can be a solution, many others are possible :
mysql> select distinct the_date, person_id, cost, name
-> from trips,persons
-> where person_id=persons.id
-> and the_date in(select max(the_date) from trips a
-> where a.person_id=person_id
-> group by person_id)
-> ;
+------------+-----------+------+------+
| the_date | person_id | cost | name |
+------------+-----------+------+------+
| 2005-02-08 | 1 | 580 | john |
| 2005-01-25 | 2 | 200 | jane |
| 2005-02-03 | 3 | 600 | mike |
| 2005-02-20 | 4 | 320 | mary |
+------------+-----------+------+------+
4 rows in set (0.00 sec)
Mathias
Selon René Fournier <[EMAIL PROTECTED]>:
> I'm having a really hard time selecting rows from a table in one SELECT
> statement. I can do it in two SELECTS, but it seems I should be able to
> do it in one.
>
> TRIPS
>
> id date person_id cost
> ---------------------------------------------------------------
> 1 2005-01-01 2 500
> 2 2005-01-05 1 400
> 3 2005-01-12 4 350
> 4 2005-01-15 3 175
> 5 2005-01-17 2 385
> 6 2005-01-25 2 200
> 7 2005-02-03 3 600
> 8 2005-02-08 1 580
> 9 2005-02-20 4 320
>
> PERSONS
>
> id name
> -------------------------
> 1 john
> 2 jane
> 3 mike
> 4 mary
> 5 henry
>
>
> Okay, I want to select from Trips the most recent trip for each person.
> As you can see, some of the Persons have travelled more than once, but
> I only want the last trip each one made. Also, not everyone in Persons
> has made a trip (Henry).
>
> Here's the output I'm looking for:
>
> 2005-02-20 4 320 mary
> 2005-02-08 1 580 john
> 2005-02-03 3 600 mike
> 2005-01-25 2 200 jane
>
>
> I've written and re-written my SELECT queries numerous times, but
> can't seem to retrieve just one, most-recent trip/person. Any ideas?
>
> ...Rene
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
