Reni Fournier wrote:
Thanks for the solution. It looks like it would work, but I don't have
MySQL 4.1 (which I believe is required for this to work, since this is
SUBSELECT, isn't it?).
Assuming I have to use two selects, which would you say is faster,
creating a temporary table in MySQL, or extracting the data back into
PHP, and recreating a SELECT from there?
by using in memory temporary table, you'll avoid the round trip between,
the PHP server and the SQL server, which would be a bit faster.
..Rene
On 2-Jun-05, at 2:28 PM, [EMAIL PROTECTED] wrote:
Hi Reni,
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 Reni 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]
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
