Re: mysql: A Simple Question on SQL

[HUI Chun Kit]

Dear Monty and all,

	I have asked you this question sometimes ago and thanks for your
answer which works fine. But now I encounter another similar problem.
However, this time I have status_id replaced by a string column. Then I
found that max(concat(.....)) no longer works. What should I do.
	To help u recall the questions, I paste it here again.

Best Rgds,
Jacky Hui

The original question:
-----------------------------------------------------------
Table history :
        --------------------------------------
        |event_id | status_id | status_TS    |
        --------------------------------------
        | 1       |   2       |19990222042811|
        | 1       |   3       |19990222042224|
        | 1       |   4       |19990222042914|
        | 1       |   2       |19990222042924|
        | 2       |   3       |19990223048905|
        | 2       |   4       |19990224046906|
        | 2       |   5       |19990225043907|
        | 3       |   3       |19990222242404|
        | 3       |   3       |19990222442955|
        | 4       |   4       |19990222662408|
        | 5       |   5       |19990222078834|
	..........

        This table is the history of change of a certain event with
event_id (int).
        If I want to view the history of a particular event, I can
        select * from history where event_id="..." order by status_TS

        If I want to view the most recent status of a pacticular event, I
can
        select * from history where event_id="..." order by status_TS DESC
LIMIT 1;

        But if I want to list the most recent status of each events. What
should I do??
----------------------------------------------------------





On Thu, 25 Feb 1999, Michael Widenius wrote:

> Sorry, by typo: Try this:
> 
> select event_id,substring(max(concat(status_TS,status_id)),15),max(status_TS) from
> history group by event_id;
> 
> This works, because the 'max(concat(status_TS,status_id))' should
> contain the id for the maximum status_TS.
> 
> Regards,
> Monty
>