Re: SELECT problem and QUESTION OF SPEED
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]
Re: SELECT problem and QUESTION OF SPEED
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? ...Rene On 2-Jun-05, at 2:28 PM, [EMAIL PROTECTED] wrote: 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 dateperson_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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem and question
admin, Wednesday, February 06, 2002, 4:40:30 AM, you wrote: a hello, a This is my problem and question: a I installed mysql-3.23.36 when I installed the RedHat7.1, but now I want to install mysql-3.23.47 to the platform and I hope the two versions are both useful. How should I configure the a parameters when I configure? You can find info about it in the manual, look at: http://www.mysql.com/doc/I/n/Installing_many_servers.html and http://www.mysql.com/doc/M/u/Multiple_servers.html a I want mysql supporting the BDB tables as well,is that using '--with-berkeley-db=./bdb' enough? Get some more info about BDB tables here: http://www.mysql.com/doc/B/D/BDB_install.html and http://www.mysql.com/doc/B/D/BDB_start.html a I'm waiting for your replying and thank you for that! a admin a [EMAIL PROTECTED] -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
problem and question
admin, Wednesday, February 06, 2002, 4:40:30 AM, you wrote: a hello, a This is my problem and question: a I installed mysql-3.23.36 when I installed the RedHat7.1, but now I want to install mysql-3.23.47 to the platform and I hope the two versions are both useful. How should I configure the a parameters when I configure? You can find info about it in the manual, look at: http://www.mysql.com/doc/I/n/Installing_many_servers.html and http://www.mysql.com/doc/M/u/Multiple_servers.html a I want mysql supporting the BDB tables as well,is that using '--with-berkeley-db=./bdb' enough? Get some more info about BDB tables here: http://www.mysql.com/doc/B/D/BDB_install.html and http://www.mysql.com/doc/B/D/BDB_start.html a I'm waiting for your replying and thank you for that! a admin a [EMAIL PROTECTED] -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
problem and question
hello, This is my problem and question: I installed mysql-3.23.36 when I installed the RedHat7.1, but now I want to install mysql-3.23.47 to the platform and I hope the two versions are both useful. How should I configure the parameters when I configure? I want mysql supporting the BDB tables as well,is that using '--with-berkeley-db=./bdb' enough? I'm waiting for your replying and thank you for that! admin [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
