Selecting the last added row from the database

Hello, there is a ticket system. There is only one table for it in the database.

like this:

id | pid | title | msg | time

The client creates a ticket with pid=0, it is answered by managers and all replies have pid=ID to the ticket the client created. Example:

1 | 0 |  |   
2 | 1 | RE: |    1
3 | 1 | RE: |    2
4 | 1 | RE: |    3
5 | 1 | RE: |    4
6 | 1 | RE: |    5

I need to select all rows with pid=0 and their latest replies. In this case, the result should be the first line and the 6th


Answer 1, authority 100%

This is some kind of tin))

SELECT 
  T1.id AS ticket_id,
  T1.title AS ticket_title,
  T1.msg AS ticket_msg,
  T1.user_id AS ticket_user,
  T2.id AS answer_id,
  T2.user_id AS answer_user,
  T2.manager_id AS answer_manager,
  T2.time AS answer_time
FROM thetable AS T1
LEFT OUTER JOIN thetable AS T2 ON (
 (T2.pid=T1.id) AND
 (T2.id=( SELECT MAX( id ) FROM thetable  WHERE pid = T1.id ) )
  )
WHERE T1.pid =0
GROUP BY T1.id

I advise you to test something from phpadmin first. There will be errors – issue them here.


Answer 2, authority 50%

select * from thetable  where pid=0
union
select t1.*
from thetable  t1
join (select id, times
from thetable 
where pid!=0
order by time desc limit 0,1) as t2 on t2.id=t1.id

Not having MySQL at hand, I checked with MSSQL. The complete script is like this

set dateformat dmy  
set nocount on  
create table #t (id int, pid int, times datetime)  
insert into #t values(1, 0, '1-10-2011 12:00')  
insert into #t values(2, 1, '1-10-2011 12:30')  
insert into #t values(3, 1, '1-10-2011 12:20')  
insert into #t values(4, 0, '1-10-2011 12:21')
select * from #t where pid=0
union
select t1.*
from #t t1
join (select top 1 id, times
from #t
where pid!=0
order by times desc) as t2 on t2.id=t1.id
drop table #t

Answer:

1 0 2011-10-01 12:00:00.000
2 1 2011-10-01 12:30:00.000
4 0 2011-10-01 12:21:00.000

So what – the answer is quite meaningful.


Answer 3, authority 50%

Something like this, the request was not checked, but I hope you understand the idea:

SELECT x.* FROM thetable x,(SELECT MAX(id) as mid,pid FROM  thetable GROUP BY pid) t
WHERE x.id != 0 AND x.id = t.mid AND x.pid = t.pid

The result is the rows with the latest answers + add 1 more trivial subquery and you get what you need…


Answer 4

It could have been easier

select * from table where pid = '0' order by 'id' desc

or so

select * from table where pid = '0' order by 'id' desc limit 10

Next echo " .$row['title']., .$row['msg'], , .$row['user_id'].";