Distinct field selection? 1

[adder1]

Situation: I have a table structured as such:
ticket_id date time field2

What I am trying to do is take the table and strip out all duplicates of ticket_id, keeping the most recent according to the time field.

Of course, I can do a SELECT DISTINCT on just the ticket_id field, and I get just the ticket_id results, however, I'm having trouble being able to get the remainder of the fields like this.

Any help would be appreciated in getting this working!

 

[mikerexx]

select *
from t1 main
where date = (
select max(date)
from t1 sub
where main.ticket_id = sub.ticket_id )

 

[blom0344]

or:

select temp.* from 
(select ticket_id, date , time, field2,
rank() over (partition by ticket_id order by date desc) as rk from table) temp
where temp.rk = 1

Ties Blom

 

[adder1]

mikerexx, your code worked perfectly! (after a minor addition to add a Time max after the Date max)

Thank you so very much!