Re: How to select the max value
Yes, I'm using 4.1.x and didn't think of doing the query that way. You have been extremely helpfull Thanks! On Tue, 2005-04-12 at 21:44, Rhino wrote: What version of MySQL are you using? If you are running 4.1.x or 5.0.x, you should be able to do this subquery to get the row you want. select id, col_x, date_col from table_x where date_col = (select max date_col from table_x) The subquery gets the max (latest) date in the table, then the outer query finds the row that has that date on it.) If there are several rows with the same max date, the outer query will return all of them. Please note that I am running MySQL 4.0.x so I can't test this in MySQL but it would work in DB2; DB2 and MySQL are very close in most respects. Rhino - Original Message - From: Mauricio Pellegrini [EMAIL PROTECTED] To: MySql List mysql@lists.mysql.com Sent: Tuesday, April 12, 2005 7:35 PM Subject: How to select the max value Hi, I need to select the max value from a set of records but I also need the primary key for that record. The problem is that the record id may not be the same as the record max value for the column as in the following example: Table_x Id x_col date_col 1 1 2005-04-11 2 1 2005-03-10 3 1 2005-04-12 4 1 2001-01-01 with SELECT id, x_col, max(date_col) FROM table_x GROUP BY x_col I would probably get the following result Id x_col date_col 4 1 2005-04-12 and what I would like to get is Id x_col date_col 3 1 2005-04-12 Is there a way to do that ? Thanks in advance Mauricio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.7 - Release Date: 12/04/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.7 - Release Date: 12/04/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to select the max value
Hi, I need to select the max value from a set of records but I also need the primary key for that record. The problem is that the record id may not be the same as the record max value for the column as in the following example: Table_x Id x_col date_col 1 1 2005-04-11 2 1 2005-03-10 3 1 2005-04-12 4 1 2001-01-01 with SELECT id, x_col, max(date_col) FROM table_x GROUP BY x_col I would probably get the following result Id x_col date_col 4 1 2005-04-12 and what I would like to get is Id x_col date_col 3 1 2005-04-12 Is there a way to do that ? Thanks in advance Mauricio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to select the max value
Mauricio Pellegrini wrote: Hi, I need to select the max value from a set of records but I also need the primary key for that record. The problem is that the record id may not be the same as the record max value for the column as in the following example: Table_x Id x_col date_col 1 1 2005-04-11 2 1 2005-03-10 3 1 2005-04-12 4 1 2001-01-01 with SELECT id, x_col, max(date_col) FROM table_x GROUP BY x_col I would probably get the following result Id x_col date_col 4 1 2005-04-12 and what I would like to get is Id x_col date_col 3 1 2005-04-12 That *is* what you'd get. The max() function works on an individual column, not on all columns at once. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to select the max value
What version of MySQL are you using? If you are running 4.1.x or 5.0.x, you should be able to do this subquery to get the row you want. select id, col_x, date_col from table_x where date_col = (select max date_col from table_x) The subquery gets the max (latest) date in the table, then the outer query finds the row that has that date on it.) If there are several rows with the same max date, the outer query will return all of them. Please note that I am running MySQL 4.0.x so I can't test this in MySQL but it would work in DB2; DB2 and MySQL are very close in most respects. Rhino - Original Message - From: Mauricio Pellegrini [EMAIL PROTECTED] To: MySql List mysql@lists.mysql.com Sent: Tuesday, April 12, 2005 7:35 PM Subject: How to select the max value Hi, I need to select the max value from a set of records but I also need the primary key for that record. The problem is that the record id may not be the same as the record max value for the column as in the following example: Table_x Id x_col date_col 1 1 2005-04-11 2 1 2005-03-10 3 1 2005-04-12 4 1 2001-01-01 with SELECT id, x_col, max(date_col) FROM table_x GROUP BY x_col I would probably get the following result Id x_col date_col 4 1 2005-04-12 and what I would like to get is Id x_col date_col 3 1 2005-04-12 Is there a way to do that ? Thanks in advance Mauricio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.7 - Release Date: 12/04/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.7 - Release Date: 12/04/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to select the max value
Daniel Kasak wrote: Mauricio Pellegrini wrote: Hi, I need to select the max value from a set of records but I also need the primary key for that record. The problem is that the record id may not be the same as the record max value for the column as in the following example: Table_x Idx_col date_col 1 1 2005-04-11 2 1 2005-03-10 3 1 2005-04-12 4 1 2001-01-01 with SELECT id, x_col, max(date_col) FROM table_x GROUP BY x_col I would probably get the following result Idx_col date_col 4 1 2005-04-12 and what I would like to get is Idx_col date_col 3 1 2005-04-12 That *is* what you'd get. The max() function works on an individual column, not on all columns at once. Oops. Disregard. Didn't think that one through. See Rhino's response. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to write query to select the Max(version) for each unique file_name record?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Sunday 20 July 2003 22:59, Tanamon wrote: ++---+--+ | id | file_name | max(version) | ++---+--+ | 6 | ffour |2 | | 1 | fone |3 | | 5 | fthree|1 | | 2 | ftwo |2 | ++---+--+ 4 rows in set (0.02 sec) The query below returns the empty set. mysql select id, file_name, max(version) from test group by file_name having max(version); Empty set (0.00 sec) Try this: Select id, file_name, max(version) from test group by file_name; The having clause in your query is redundant as the max() aggregate already limits it to the maximum value. - ---Michael -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) iD8DBQE/G8GujeziQOokQnARArVPAJ9TdchBj8tExxitns8PkRSnGbMFugCgmhTW et7HpBp07nWh+KD//xqDLbM= =p8/8 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to write query to select the Max(version) for each unique file_name record?
Hello All, I am a MySQL newbie trying to write a query that selects file_name records possessing the highest numbered version for that unique file_name. I show sample data and two trial queries below. Logically I want to use max(version) as a constraint in a Where Clause. However, the max() function is not allowed directly in a where clause. I have contemplated a second table to track the max version for each file name. I would like to structure the data in an efficient manner for query performance when the data set grows to many thousands of unique file_name records with many hundreds of versions each. Any guidance will be appreciated. David Oberlitner Sample data: mysql select * from test; ++---+-+ | id | file_name | version | ++---+-+ | 1 | fone | 1 | | 2 | ftwo | 1 | | 3 | fone | 2 | | 4 | fone | 3 | | 5 | fthree| 1 | | 6 | ffour | 1 | | 7 | ftwo | 2 | | 8 | ffour | 2 | ++---+-+ The query below gets close in that it returns each file name and its max(version), however it returns the ID field associated with the first record instance of file_name and not the ID associated with the max(version) instance of the file_name record. mysql select id, file_name, max(version) from test group by file_name; ++---+--+ | id | file_name | max(version) | ++---+--+ | 6 | ffour |2 | | 1 | fone |3 | | 5 | fthree|1 | | 2 | ftwo |2 | ++---+--+ 4 rows in set (0.02 sec) The query below returns the empty set. mysql select id, file_name, max(version) from test group by file_name having max(version); Empty set (0.00 sec) Additionally, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select and =MAX()
Jorge When I issue your suggested commands here are the resulted rows.. mysql SELECT price -FROM shop -HAVING price=MAX(price); Empty set (0.01 sec) But when I issue... mysql select price from shop order by price desc; +---+ | price | +---+ | 19.95 | | 10.99 | | 3.99 | | 3.45 | | 1.69 | | 1.45 | | 1.25 | +---+ 7 rows in set (0.01 sec) Why?? - 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
RE: Select and =MAX()
Hi, What version of MySQL are you using ? How is price defined ? .. In the below example, I defined price as float(9,2). mysql SELECT * - FROM a; +---+ | price | +---+ | 19.95 | | 10.99 | | 3.99 | | 3.45 | | 1.69 | | 1.45 | | 1.25 | +---+ 7 rows in set (0.01 sec) mysql SELECT price - FROM a - HAVING price=MAX(price); +---+ | price | +---+ | 19.95 | +---+ 1 row in set (0.01 sec) mysql Regards, Jorge For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Jorge del Conde [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Mexico City, Mexico ___/ www.mysql.com -Original Message- From: bobby [mailto:bobby] On Behalf Of R Talbot Sent: Wednesday, June 27, 2001 8:21 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Select and =MAX() Jorge When I issue your suggested commands here are the resulted rows.. mysql SELECT price -FROM shop -HAVING price=MAX(price); Empty set (0.01 sec) But when I issue... mysql select price from shop order by price desc; +---+ | price | +---+ | 19.95 | | 10.99 | | 3.99 | | 3.45 | | 1.69 | | 1.45 | | 1.25 | +---+ 7 rows in set (0.01 sec) Why?? - 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
Select and = MAX()
Following the Tutorial Creating and populating the table Shop in the db Test.. Why won't the following syntax work? mysql select price - from shop - where price=Max(price) ; ERROR : Invalid use of group function Bob T - 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
Re: Select and = MAX()
Why bother when the following works: mysql SELECT price FROM shop ORDER BY price DESC LIMIT 1; On Tue, 26 Jun 2001, R Talbot wrote: Following the Tutorial Creating and populating the table Shop in the db Test.. Why won't the following syntax work? mysql select price - from shop - where price=Max(price) ; ERROR : Invalid use of group function Bob T - 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
RE: Select and = MAX()
Hi, You are using an aggregate function (MAX) in a condition which requires you to use the HAVING statement instead of the WHERE statement. Your query should look something like: SELECT price FROM shop HAVING price=MAX(price); Regards, Jorge For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Jorge del Conde [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Mexico City, Mexico ___/ www.mysql.com -Original Message- From: bobby [mailto:bobby] On Behalf Of R Talbot Sent: Tuesday, June 26, 2001 7:16 PM To: [EMAIL PROTECTED] Subject: Select and = MAX() Following the Tutorial Creating and populating the table Shop in the db Test.. Why won't the following syntax work? mysql select price - from shop - where price=Max(price) ; ERROR : Invalid use of group function Bob T - 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
Re[2]: Select and = MAX()
For best performance i prefer this way (it's faster): SELECT MAX(price) FROM shop; rather than SELECT price FROM shop ORDER BY price DESC LIMIT 1; -- In second case MySQL must select all records from table, sort it and then apply LIMIT (it's must be logicaly slower than first one) Michal Dvoracek [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
