To get the one record with the MAX() value in a field, I would do:
SELECT @maxcod:=max(cod) from #temp;
SELECT * from #temp where cod = @maxcod;
To get all of the records with the max(cod) value for each idtable value,
I would use a temp table:
create temporary table tmpMax
SELECT idtable, max(cod) as maxcod
from #temp
group by idtable;
SELECT *
FROM #temp t
INNER JOIN tmpMax tm
ON t.idtable = tm.idtable
ORDER BY Data DESC;
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"fgmmoribe" <[EMAIL PROTECTED]> wrote on 07/26/2004 10:54:30 PM:
>
> I have a table like this
>
>
+-----------+---------------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
>
+-----------+---------------------+------+-----+---------+----------------+
> | id | int(3) | | PRI | NULL |
> auto_increment |
> | idTable | int(3) unsigned | | | 0 | |
> | title | varchar(150) | YES | | NULL |
> |
> | description | varchar(150) | YES | | NULL | |
> | date | datetime | YES | | NULL | |
>
+-----------+---------------------+------+-----+---------+----------------+
>
>
> Is there anyway to make select command like this in Mysql 4.0:
> select * from #temp where cod in (select max(cod) from #temp
> group by idtable) order by data desc
>
> could someone help me?
>
> thanks
>
> Fernando
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
