CODEWITHSUNDEEP
mysqlsql
Seal
Seal

Reputation: 1060

Finding the most recent timestamp per event

My table has the following columns, |id|incident_number|created_at|note|.

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?

Upvotes: 0

Views: 166

Answers (2)

SebastianH
SebastianH

Reputation: 2182

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.

Upvotes: 2

Seal
Seal

Reputation: 1060

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

Upvotes: 0

Related Questions