Part of my job is desigining helper applications around a set of data in a database designed by a third-party software vendor. The vendor’s database design is … interesting to say the least; many of the records are versioned<\/i>, so a common challenge is to find the newest record for a given entity.
\n<\/p>\n
This isn’t always as easy as it sounds, because determining “newest” often requires looking at multiple columns. To understand, consider the following (contrived) example: <\/p>\n
\r\n-- TABLE values\r\n\r\n collection | book | chapter | line | value \r\n------------+------+---------+------+-------\r\n 1234 | 3 | 2 | 34 | fizz\r\n 1234 | 1 | 17 | 23 | blah\r\n 1235 | 2 | 12 | 7 | quuz\r\n 1235 | 7 | 8 | 44 | qaax\r\n 1235 | 7 | 9 | 2 | fooz\r\n<\/pre>\nIn this table, we have values that are found within lines, within chapters, within books, within a collection. This might be entries in some kind of deed book or log, for example. Suppose we want to generate a table containing only the newest value – that is, the value found in the last line of the last chapter of the last book in each collection. Correct results would be: <\/p>\n
\r\n collection | value \r\n------------+-------\r\n 1234 | fizz\r\n 1235 | fooz\r\n<\/pre>\n\nA first approach<\/h2>\n
\nAt first blush, this seems simple, and you might be tempted to try something like this: <\/p>\n
\r\nSELECT collection, value \r\nFROM values v \r\nWHERE book=(SELECT MAX(book) FROM values WHERE collection = v.collection) AND \r\n chapter=(SELECT MAX(chapter) FROM values WHERE collection = v.collection) AND\r\n line = (SELECT MAX(line) FROM values WHERE collection = v.collection)\r\n<\/pre>\nProblem is, this doesn’t work; in fact it returns no rows. Why? <\/p>\n
Consider collection 1234: <\/p>\n
\n
MAX(book)<\/code> is 3\n<\/li>\nMAX(chapter)<\/code> is 17\n<\/li>\nMAX(line)<\/code> is 34\n<\/li>\n<\/ul>\n…which means we’re looking for a record with
book=1<\/code>,chapter=17<\/code>, andline=34<\/code>. No such record exists. <\/p>\nOne possible approach to the problem is a “fractal” approach, in which each subsequent
WHERE<\/code> subclause needs to include the previous ones: <\/p>\n\r\nSELECT collection, value \r\nFROM values v \r\nWHERE book=(SELECT MAX(book) FROM values WHERE collection = v.collection) \r\n AND chapter=(SELECT MAX(chapter) \r\n\t\t FROM values WHERE collection = v.collection \r\n\t\t AND book=(SELECT MAX(book) \r\n\t\t\t FROM values WHERE collection = v.collection)) \r\n AND line = (SELECT MAX(line) \r\n\t\t FROM values WHERE collection = v.collection \r\n\t\t AND chapter = (SELECT MAX(chapter) \r\n\t\t\t\t FROM values WHERE collection = v.collection \r\n\t\t\t\t AND book=(SELECT MAX(book) \r\n\t\t\t\t\t FROM values \r\n\t\t\t\t\t WHERE collection = v.collection))\r\n\t\t\t\t AND book = (SELECT MAX(book) \r\n\t\t\t\t\t FROM values \r\n\t\t\t\t\t WHERE collection= v.collection))\r\n<\/pre>\nYUCK! What a mess! It does<\/i> work, but it’s confusing and tedious to write; can you imagine this query if there were four or five fields we need to order? Or if
values<\/code> was some complex subquery rather than a table? I’ve had to write queries like this on other database systems where this dataset has lived, and they’re some of the most hideous I’ve ever written. It’s bad enough with a simple example table, but in a real query situation it’s enough to make your head spin. <\/p>\n<\/div>\n<\/div>\n\nA different approach<\/h2>\n
\nIf you only need to find the latest value for a single collection<\/i>, there’s a much easier trick: <\/p>\n
\r\nSELECT collection, value \r\nFROM values \r\nWHERE collection=1234\r\nORDER BY book DESC, chapter DESC, line DESC \r\nLIMIT 1;\r\n<\/pre>\n
ORDER BY<\/code> works exactly how we want it to in this case: it orders first bybook<\/code>, then bychapters<\/code> withinbooks<\/code>, then bylines<\/code> withinchapters<\/code>. By ordering in descending order and just snipping off the first result, we end up with just the newestvalue<\/code>. <\/p>\nUnfortunately, we can’t use this approach to build a table of results for all collections. Or can we? <\/p>\n<\/div>\n
\nEnter window functions<\/h3>\n
\n