.
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
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
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
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
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
Id
|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
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
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 |
]; [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
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
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
[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
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
13 matches
Mail list logo
