SQL获取分组数据中max/first/least列的方法（转载备份）

这几天在做一个和oracle相关的项目的时候，遇到一个问题，我需要一次性查出一个表里每一个分组的最新的一条记录，于是想到了group by，处理后，可以获得每个分组最新的记录的时间，但是下一步卡住了，后来在网上查到了一个相关的解决方案，作为技术备份，先记录在这里。

原始链接：http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

?

PS：因为我用的oracle数据库，这个是mysql数据库，所以在第一个解决方法

set @num := 0, @type := '';select type, variety, price,      @num := if(@type = type, @num + 1, 1) as row_number,      @type := type as dummyfrom fruits force index(type)group by type, price, varietyhaving row_number <= 2;

Let’s see if that works:

+--------+----------+-------+------------+--------+
| type?? | variety? | price | row_number | dummy? |
+--------+----------+-------+------------+--------+
| apple? | fuji???? |? 0.24 |????????? 1 | apple? |
| apple? | gala???? |? 2.79 |????????? 2 | apple? |
| cherry | bing???? |? 2.55 |????????? 1 | cherry |
| cherry | chelan?? |? 6.33 |????????? 2 | cherry |
| orange | valencia |? 3.59 |????????? 1 | orange |
| orange | navel??? |? 9.36 |????????? 2 | orange |
| pear?? | bartlett |? 2.14 |????????? 1 | pear?? |
| pear?? | bradford |? 6.05 |????????? 2 | pear?? |
+--------+----------+-------+------------+--------+
Ah, now we’re cooking! It did what I wanted, without a filesort or temporary table. Another way to do this, by the way, is to take variety out of the GROUP BY so it uses the index on its own. Because this selects a non-grouped column from a grouped query, this only works if you are running with ONLY_FULL_GROUP_BY mode turned off, which I hope you are not doing without good reason.

Other methods

Be sure to check the comments for user-contributed methods. There are some really novel approaches. I always learn so much from your comments… thank you!

Conclusion

Well, that’s it. I’ve shown you several ways of solving the common “get the extreme row from each group” query, and then moved on to how you can get the top N rows from each group in various ways. Then I dove into MySQL-specific techniques which some (including myself, depending on my mood) would regard as mildly foolish to utterly stupid. But if you need the last bit of speed out of your server, you sometimes have to know when to break the rules. And for those who think this is just MySQL foolishness, it’s not; I’ve seen people desperately do these types of things on other platforms too, such as SQL Server. There are hacks and tweaks on every platform, and people who need to use them.

?