SELECT * FROM (
SELECT  tickets.t_number ,
        tickets.t_reference ,
        ticket_events.te_event ,
        ticket_events.te_date,
        ROW_NUMBER() OVER (PARTITION BY ticket_events.te_event ORDER BY ticket_events.te_date DESC) RN
FROM    ticket_events
        INNER JOIN tickets ON ticket_events.te_system_ref = tickets.t_number
        INNER JOIN warrants_due ON tickets.t_number = warrants_due.wd_system_ref
WHERE   tickets.t_number IN ( 1091421, 1091426, 1091429, 1091430, 1091432,
                              1091450 )
) s WHERE RN = 1;  

quote:

---

Originally posted by AfcDons

Hi James,

Thanks for the reply but I didn't explain it very well.
I have a table with Tickets and each of these tickets have a series of events that can happen to them stored in the events table. I need to pull out just the latest event for each ticket by the event date. I have 25,000 tickets to do this to. I know that MAX will bring up the latest date but I don't know where to put the MAX statement so I get all 25,000 results. I hope this is a little clearer.
Thanks again for the help.

John

---

SELECT tickets.t_number, tickets.t_reference,ticket_events.te_event, ticket_events.te_date, FROM ticket_events INNER JOIN
tickets ON ticket_events.te_system_ref = tickets.t_number INNER JOIN
warrants_due ON tickets.t_number = warrants_due.wd_system_ref
WHERE tickets.t_number in (1091421,
1091426,
1091429,
1091430,
1091432,
1091450)
AND EXISTS (SELECT 1
FROM FROM ticket_events te 
INNER JOIN tickets t
ON te.te_system_ref = t.t_number
WHERE t_number = ticket.t_number
GROUP BY t.t_number
HAVING MAX(te.te_date) = ticket_events.te_event
)

quote:

---

Originally posted by AfcDons

Hi Chandu,

Thanks for this but I am getting too many returns in the resultset.
There are only six tickets so I was hoping for six results ( last event for each ticket) but I get 161 results ( all the events for each ticket)

Thanks again for your help.

John

---

SELECT t.t_number, t.t_reference,te.te_event, te.te_date
FROM ticket_events te 
INNER JOIN tickets t
ON te.te_system_ref = t.t_number 
INNER JOIN warrants_due w
ON t.t_number = w.wd_system_ref
WHERE t.t_number in (1091421,
1091426,
1091429,
1091430,
1091432,
1091450)
AND EXISTS (SELECT 1
FROM ticket_events te1 
INNER JOIN tickets t1
ON te1.te_system_ref = t1.t_number
WHERE t1.t_number = t.t_number
GROUP BY t1.t_number
HAVING MAX(te1.te_date) = te.te_event
)

quote:

---

Originally posted by AfcDons

Hi visakh

I had to change the last bit
HAVING MAX(te1.te_date) = te.te_event to
HAVING MAX(te1.te_date) = te.te_date

And it is now working. Thanks very much for this. It will help me in the future.

Thanks to everyone else who replied

John

---