Subselecting the most current record from a history table in SQL
Google is a great thing for finding out how to do stuff that you cannot be bothered to figure out. So this post is designed to help anyone that has the above problem. Hopefully it will find its way into the index and be of some use to someone who wants to know.
The basic problem is this. Given a table that looks like this:
How do you select the most current record from such a structure using a single query?
I puzzled over it briefly, but ended up with a current record table for speed and ease of use. However when it came to reporting, the log table was useless without a good answer.
So here is the SQL:
SELECT * FROM table1 A WHERE
(
SELECT MAX (B.ID) FROM table1 B WHERE A.ItemID=B.ItemID
) = A.ID
This will allow you to pull off the top ID's for any particular historical record, and return a results set of the most current. In addition if you have more refining clauses you may be better putting them in the subselect and the parent select for performance.
This mechanism can also be used if you have a modified date to pick the record closest to a date.
That SQL would look like (assuming a column called modified has been added!):
SELECT * FROM table1 A WHERE A.Modified <'10-10-2000' and
(
SELECT MAX (B.ID) FROM table1 B WHERE A.ItemID=B.ItemID and B.Modified <'10-10-2000'
) = A.ID
So there you go. Simple really, but actually extracting how to do that from the descriptions of the Select command is a bit of a challenge.
There are many other cool things you can do with this type of structure, but it certainly helped my historic reporting!
The basic problem is this. Given a table that looks like this:
| ID | ItemID | Description |
| 1 | ID1 | Historic |
| 2 | ID1 | Latest for ID1 |
| 3 | ID2 | Historic ID2 |
| 4 | ID2 | Historic ID2 |
| 5 | ID2 | Current ID2 |
| 6 | ID3 | Only one ID3 |
How do you select the most current record from such a structure using a single query?
I puzzled over it briefly, but ended up with a current record table for speed and ease of use. However when it came to reporting, the log table was useless without a good answer.
So here is the SQL:
SELECT * FROM table1 A WHERE
(
SELECT MAX (B.ID) FROM table1 B WHERE A.ItemID=B.ItemID
) = A.ID
This will allow you to pull off the top ID's for any particular historical record, and return a results set of the most current. In addition if you have more refining clauses you may be better putting them in the subselect and the parent select for performance.
This mechanism can also be used if you have a modified date to pick the record closest to a date.
That SQL would look like (assuming a column called modified has been added!):
SELECT * FROM table1 A WHERE A.Modified <'10-10-2000' and
(
SELECT MAX (B.ID) FROM table1 B WHERE A.ItemID=B.ItemID and B.Modified <'10-10-2000'
) = A.ID
So there you go. Simple really, but actually extracting how to do that from the descriptions of the Select command is a bit of a challenge.
There are many other cool things you can do with this type of structure, but it certainly helped my historic reporting!
Comments