Finding the most recent timestamp per event

Question

so each incident can have many updates, updates are categorized by ID, and are given a created_at time in the form of 2014-01-30 21:11:56. So one incident_number can have many id's and many created_at's depending on how often it is being updated.

What I am trying to do is filter out all of the other created_at's by the most recent one in correspondence to time.

The catch is, is when running the query i cant look at the data and say if the created_at is less than this given date because I am unsure of when these incidents have been worked on.

big picture is I will be able to show each incident with the most recent update, sorted by the created_at date.

Any suggestions?

SebastianH · Answer 1 · 2014-02-06T09:26:23.730

2

I got a bit lost halfway through your question, but this might give you an idea:

SELECT MAX(created_at) FROM ... GROUP BY incident_number

EDIT: I add this due to your comments.

Yes, MAX returns only the highest value. My statement was to help you find that value and add the others as you see fit. I guess what you need is something like:

SELECT MAX(created_at), incident_number, note FROM ... GROUP BY incident_number, note

That shows for each incident only the row with the most recent created_at timestamp.

EDIT2: As you mentioned an error I checked my SQL. There was the column note missing in the GROUP BY clause. I dont see the necessity for a sub-select.

edited Feb 06 '14 at 09:26

answered Feb 05 '14 at 17:54

SebastianH

2,172
1
18
29

That works, but it wont let me display the other columns, it only displays the MAX(created_at) – Seal Feb 05 '14 at 18:08
I also tried sorting by MAX(created_at), that doesnt work, gives an error. – Seal Feb 05 '14 at 18:16
@user2908778 Okay, I see you need further hints. I updated the answer. – SebastianH Feb 05 '14 at 18:21
haha, no that was the first thing I tried. It returns an error. – Seal Feb 05 '14 at 19:30
@user2908778 I updated again. You have to add all columns you want to select to the GROUP BY clause except the one you are using aggregation (like MAX, SUM, ...) on. I forgot `note` in my example above. – SebastianH Feb 06 '14 at 09:27
I posted my solution. – Seal Feb 06 '14 at 14:36
@user2908778 I know, but your solution is way too complicated =) – SebastianH Feb 06 '14 at 17:13

score 0 · Accepted Answer · answered Feb 05 '14 at 21:01

Ok, so what I ended up having to do was create a sub-query for the created_at column. Final query looks like so...

SELECT Top 1
    wn.id, wn.incident_number, wn.created_at, note 
FROM
    sn_incident_work_note wn
Where
    wn.incident_number = 'INC0005607474' 

and wn.created_at = (Select MAX(iwn.created_at)
                    from sn_incident_work_note iwn
                    where iwn.incident_number = wn.incident_number)

Order By id DESC

Finding the most recent timestamp per event

2 Answers2

Linked