Today there was a question on the Freenode MySQL channel about a classical problem: Rows holding group-wise maximum of a column. This is a problem that I keep encountering every so often, so I thought I would write up something about it.

A good example of the problem is a table like the following holding versioned objects:

CREATE TABLE object_versions (
  object_id INT NOT NULL,
  version INT NOT NULL,
  data VARCHAR(1000),
  PRIMARY KEY(object_id, version)
) ENGINE=InnoDB

Now it is easy to get the latest version for an object:

SELECT data FROM object_versions WHERE object_id = ? ORDER BY version DESC LIMIT 1

The query will even be very fast as it can use the index to directly fetch the right row:

mysql> EXPLAIN SELECT data FROM object_versions
WHERE object_id = 42 ORDER BY version DESC …

MySQL just released their new MySQL Query Analyzer (link to a trial), and recently wrote up an interview with Mark Matthews about it. If you haven’t read that article, go ahead and do it. I have not used this software, but I fully believe its functionality is quite nice.

But there is at least [...]

I've always been intrigued by the FIRST_ROWS hint, so I paid special attention when we reached it in the 11g SQL Tuning class. But I'm still puzzled.The course notes said that although you shouldn't be using hints generally, when you do, FIRST_ROWS is the most useful of the hints (in the form FIRST_ROWS(n) where you specify how many rows to optimize for). Also that it's not effective when the

In my opinion, OpenSQL Camp 2008 was a great success. (Give your opinion too.) I have to spend the next couple of days catching up on other things that have gotten delayed, but will try to write more about the experience later. In the meantime, I want to give a heartfelt thanks [...]

I have calmed down after my frustration of a few days ago.  My conscience says I was being unfair to Sun’s employees, who are just trying to make a business succeed in difficult times, and who support OpenSQL Camp, Maatkit, MySQL, and many other things that are dear to me.  To those I treated harshly, [...]

VirtualBox is really nice, but if you’re like me, maybe you found the networking confusing. There are three ways to do it, as explained by the manual, and the best way is with host interfaces, which don’t have limitations like the inability to ping and so on. I found what I think is [...]

The soft registration deadline for OpenSQL Camp 2008 has passed now; you can still attend, but you won’t be counted into meals and t-shirts. Meanwhile, we have 90 registered attendees, and a truly impressive set of sessions from heavy hitters in the database world. This conference is turning into the event I always [...]

Constraints are simultaneously one of my most favorite and least favorite Oracle Database features. They're great for keeping bad data out of the database. They're a terrible imposition on object-oriented, agile, or <insert your favorite buzzword here> coding style. They save a ton of repetitive coding, writing the same logic in different languages. Hey, we already wrote all that redundant code

I've blogged before about the new SQL synthax which is becoming available in databases and how it helps solving questions which are increasingly common.Now it's time for another example, something which doesn't come up often in a reporting environment as most tools have this feature, but can be a problem if you're building your output with a scipting language.Adding a "Total" row at the end of

Now why would I do that, especially when my blog is syndicated through it?

Simple. I’m always trying to cut back on RSS and other “stuff.” Less is more. But don’t worry — I subscribed again. Only this time, I subscribed to my own filtered version of it! I built it [...]