how do i always capture the newest record?

[bcamp1973]

for versioning i have table that always updated via INSERTs instead of UPDATEs so i have a history of previous changes and i can easily roll back. what's the most efficient way of always querying for the newest record?  i could go by the 'updated' filed which is a timestamp, or the 'id' field which auto-increments. is there a built in MySQL function for this?

[fenway]

Hmm... how are these groups of records linked?

[bcamp1973]

well, each record can have one of several 'type_id's and 'project_id's.  so, on a query i would try to capture the most recent unique type_id's of a given project_id.  does that make sense?

[fenway]

I guess if you had a covering index on ( project_id, type_id, timestamp ), you could group by / order by those 3 columns without any performance hit.

[artacus]

how do i always capture the newest record?

 

I was thinking of setting up an ambush at Sam Goodie.

 

On a serious note, order your results by id DESC LIMIT 1.

 

[fenway]

On a serious note, order your results by id DESC LIMIT 1.

I don't think that's what you mean.

[bcamp1973]

well, here are my tables...

 

NOTES_TYPES

-----------------

id

type

 

NOTES

-----------------

id

type_id

project_id

note

 

...and my query...

 

SELECT type, type_id, notes.id, note FROM notes LEFT JOIN notes_types ON notes.type_id=notes_types.id WHERE project_id=xxx GROUP BY type_id

 

So, say a note can be any of the following types

- comment (id #1)

- complaint (id #2)

- request (id #3)

 

and in the NOTES table has multiple entries of each.  I want my query to capture the most recent entry (highest notes.id #) of each of the 3 types, but not the previous entries...how does my query have to change to accomplish this?

[artacus]

I don't think that's what you mean.

 

Why not? Id is an autoinc field so the highest one will always be the most recent.

 

SELECT type, type_id, notes.id, note 
FROM notes 
JOIN (
    SELECT MAX(id) AS max_note, type_id
    FROM notes
    WHERE project_id=xxx
    GROUP BY type_id
) AS sub ON note.id = sub.max_note
LEFT JOIN notes_types ON notes.type_id=notes_types.id 
WHERE project_id=xxx 

[bcamp1973]

wow, i was way off.  ok, it's working now.  many thanks artacus!

[artacus]

No problem. Lets see what Fenway was talking about first though. Maybe he's seeing something that I'm missing.

[fenway]

No problem. Lets see what Fenway was talking about first though. Maybe he's seeing something that I'm missing.

Nothing's "missing" per se... I just don't like to use UIDs for anything other than relationships.  If you want to most recent one, use a datetime/timestamp column for sorting.  There doesn't even have to be a uid at all, and the value of this field can be anything, even if it is auto-incremented.

[bcamp1973]

fenway, thanks for following up.  i guess i thought that since the 'id' was an INT and indexed it would be much quicker to query on than the DATETIME field (even if it's indexed) but I guess not (I'm very much a newbie)? The application i'm building isn't going to get high traffic so honestly it's not a big issue, however, i'm trying to learn as I go so I reduce mistakes on future apps Can you demonstrate how you'd create that query? Would it simply be a tweak to artacus' query above?

[artacus]

I'm siding with bcamp on this one. I'm not sure if mysql handles handles dates as quickly as it does INT. But if one was faster, I'm sure it would be INT. Getting the max value from an indexed column though, will be way faster than on a non-indexed one. And I don't see the point of adding another index to do something we can already do with the PK.

[fenway]

Well, yes, you'd have to index the datetime field appropriately... I just like the queries doing what it's supposed to be doing, and not trying to tweak it for, as you say, a non-high traffic app.  It would be a minor tweak, simply getting find the record with MAX( datetimefield ) and then using it's UID in the join.

 

For example, what if the record was updated and the timestamp changed so that max(uid) <> max(timestamp)?  It's definitely possible, so why break it to begin with?

[fenway]

It comes down to this -- query what you want, and let MySQL worry about making it run quickly.  It's just like optimizer hints... it misses the point.

[artacus]

For example, what if the record was updated and the timestamp changed so that max(uid) <> max(timestamp)?

That would be correct, however, bcamp prefaced the question with:

i have table that always updated via INSERTs instead of UPDATEs

[fenway]

For example, what if the record was updated and the timestamp changed so that max(uid) <> max(timestamp)?

That would be correct, however, bcamp prefaced the question with:

i have table that always updated via INSERTs instead of UPDATEs

 

Never assume that "always" is going to stay "always" forever...