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
iddateperson_idcost
---
12005-01-012500
22005-01-051400
32005-01-124350
42005-01-153175
52005-01-172385
62005-01-252200
72005-02-033600
82005-02-081580
92005-02-204320
PERSONS
idname
-
1john
2jane
3mike
4mary
5henry
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-204320mary
2005-02-081580john
2005-02-033600mike
2005-01-252200jane
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]
