Installing specific package versions with pip

SQL Select only rows with Max Value on a Column

by Blogger 5:45:00 AM aggregate-functions greatest-n-per-group MYSQL SQL

SQL Select only rows with Max Value on a Column

I have this table for documents (simplified version here):

+------+-------+--------------------------------------+
| id   | rev   | content                              |
+------+-------+--------------------------------------+
| 1    | 1     | ...                                  |
| 2    | 1     | ...                                  |
| 1    | 2     | ...                                  |
| 1    | 3     | ...                                  |
+------+-------+--------------------------------------+

How do I select one row per id and only the greatest rev?
With the above data, the result should contain two rows: [1, 3, ...] and [2, 1, ..]. I'm using MySQL.

Currently I use checks in the while loop to detect and over-write old revs from the resultset. But is this the only method to achieve the result? Isn't there a SQL solution?

Update
As the answers suggest, there is a SQL solution, and here a sqlfiddle demo.

Update 2
I noticed after adding the above sqlfiddle, the rate at which the question is upvoted has surpassed the upvote rate of the answers. That has not been the intention! The fiddle is based on the answers, especially the accepted answer.

Answer:

At first glance...

All you need is a GROUP BY clause with the MAX aggregate function:

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

It's never that simple, is it?

I just noticed you need the content column as well.

This is a very common question in SQL: find the whole data for the row with some max value in a column per some group identifier. I heard that a lot during my career. Actually, it was one the questions I answered in my current job's technical interview.

It is, actually, so common that StackOverflow community has created a single tag just to deal with questions like that: greatest-n-per-group.

Basically, you have two approaches to solve that problem:

Joining with simple `group-identifier, max-value-in-group` Sub-query

In this approach, you first find the group-identifier, max-value-in-group (already solved above) in a sub-query. Then you join your table to the sub-query with equality on both group-identifier and max-value-in-group:

SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev

Left Joining with self, tweaking join conditions and filters

In this approach, you left join the table with itself. Equality, of course, goes in the group-identifier. Then, 2 smart moves:

The second join condition is having left side value less than right value
When you do step 1, the row(s) that actually have the max value will have NULL in the right side (it's a LEFT JOIN, remember?). Then, we filter the joined result, showing only the rows where the right side is NULL.

So you end up with:

SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;

Conclusion

Both approaches bring the exact same result.

If you have two rows with max-value-in-group for group-identifier, both rows will be in the result in both approaches.

Both approaches are SQL ANSI compatible, thus, will work with your favorite RDBMS, regardless of its "flavor".

Both approaches are also performance friendly, however your mileage may vary (RDBMS, DB Structure, Indexes, etc.). So when you pick one approach over the other, benchmark. And make sure you pick the one which make most of sense to you.

http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column

How do I specify unique constraint for multiple columns in MySQL?

by Blogger 5:40:00 AM composite-key database-table MYSQL unique-constraint

“INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”

by Blogger 5:38:00 AM insert MYSQL

“INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”

While executing an INSERT statement with many rows, I want to skip duplicate entries that would otherwise cause failure. After some research, my options appear to be the use of either:

ON DUPLICATE KEY UPDATE which implies an unnecessary update at some cost, or
INSERT IGNORE which implies an invitation for other kinds of failure to slip in unannounced.

Am I right in these assumptions? What's the best way to simply skip the rows that might cause duplicates and just continue on to the other rows?

answer:

I would recommend using INSERT...ON DUPLICATE KEY UPDATE.

If you use INSERT IGNORE, then the row won't actually be inserted if it results in a duplicate key. But the statement won't generate an error. It generates a warning instead. These cases include:

Inserting a duplicate key in columns with PRIMARY KEY or UNIQUE constraints.
Inserting a NULL into a column with a NOT NULL constraint.
Inserting a row to a partitioned table, but the values you insert don't map to a partition.

If you use REPLACE, MySQL actually does a DELETE followed by an INSERT internally, which has some unexpected side effects:

A new auto-increment ID is allocated.
Dependent rows with foreign keys may be deleted (if you use cascading foreign keys) or else prevent the REPLACE.
Triggers that fire on DELETE are executed unnecessarily.
Side effects are propagated to replication slaves too.

correction: both REPLACE and INSERT...ON DUPLICATE KEY UPDATE are non-standard, proprietary inventions specific to MySQL. ANSI SQL 2003 defines a MERGE statement that can solve the same need (and more), but MySQL does not support the MERGE statement.

A user tried to edit this post (the edit was rejected by moderators). The edit tried to add a claim that INSERT...ON DUPLICATE KEY UPDATE causes a new auto-increment id to be allocated. It's true that the new id is generated, but it is not used in the changed row.

See demonstration below, tested with Percona Server 5.5.28. The configuration variable innodb_autoinc_lock_mode=1 (the default):

mysql> create table foo (id serial primary key, u int, unique key (u));
mysql> insert into foo (u) values (10);
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   10 |
+----+------+

mysql> show create table foo\G
CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `u` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u` (`u`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

mysql> insert into foo (u) values (10) on duplicate key update u = 20;
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   20 |
+----+------+

mysql> show create table foo\G
CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `u` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u` (`u`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

The above demonstrates that the IODKU statement detects the duplicate, and invokes the update to change the value of u. Note the AUTO_INCREMENT=3 indicates an id was generated, but not used in the row.

Whereas REPLACE does delete the original row and inserts a new row, generating and storing a new auto-increment id:

mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   20 |
+----+------+
mysql> replace into foo (u) values (20);
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  3 |   20 |
+----+------+

http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update

All In One Script

Installing specific package versions with pip