get last records details with a subselect? 2

[webfux]

Hi,
I don´t know any more how to solve my problem.

I have a ticketsystem where each ticket belongs to an user and each user can insert a couple of messages to one ticket. Therefore I have implemented a date field (used as primary key).
Now I want to get details from the last entry belongs to a ticketid.

kdn_message:
updated (date)
ticketid (int)
kdnr (int)
detail
state

select * from kdn_message t
where updated in (select max(updated) as updated from kdn_message group by ticketid where ticketid=t.ticketid order by updated desc)

what is wrong i this statement?
using: MySQL 4.0.22

 

[TonyGroves]

You can't use sub-selects in MySQL before version 4.1.

 

[webfux]

first thx,

is there an other way to manage the problem

 

[r937]

select t1.updated 
     , t1.ticketid 
     , t1.kdnr 
     , t1.detail 
     , t1.state
  from kdn_message as t1
inner
  join kdn_message as t2  
    on t1.ticketid
     = t2.ticketid
group
    by t1.updated 
     , t1.ticketid 
     , t1.kdnr 
     , t1.detail 
     , t1.state
having t1.updated
     = max(t2.updated)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[webfux]

thx a lot dear r937, master.

It works greate!

 

[nitro2005]

why so difficult query?

SELECT * FROM kdn_message ORDER BY updated DESC LIMIT 0,1

this should return the last one record from kdn_message by date (updated).
if you want to get the last record from specific ticketid:
SELECT * FROM kdn_message WHERE ticketid=1234 ORDER BY updated DESC LIMIT 0,1
where 1234 is the id of ticket you need.

 

[r937]

nitro, he wants the last one for each ticketid !



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[nitro2005]

ahh