[征集]分组取最大N条记录方法征集，及散分(新年，升星)
2009 的最后一天，升了一颗星，同时也祝大家新年快乐。

由于技术版不能散分。所以在此征集 <分组取最大N条记录>

create table t2 (
  id int primary key,
  gid char,
  col1 int,
  col2 int
) engine=myisam;

insert into t2 values 
(1,'A',31,6),
(2,'B',25,83),
(3,'C',76,21),
(4,'D',63,56),
(5,'E',3,17),
(6,'A',29,97),
(7,'B',88,63),
(8,'C',16,22),
(9,'D',25,43),
(10,'E',45,28),
(11,'A',2,78),
(12,'B',30,79),
(13,'C',96,73),
(14,'D',37,40),
(15,'E',14,86),
(16,'A',32,67),
(17,'B',84,38),
(18,'C',27,9),
(19,'D',31,21),
(20,'E',80,63),
(21,'A',89,9),
(22,'B',15,22),
(23,'C',46,84),
(24,'D',54,79),
(25,'E',85,64),
(26,'A',87,13),
(27,'B',40,45),
(28,'C',34,90),
(29,'D',63,8),
(30,'E',66,40),
(31,'A',83,49),
(32,'B',4,90),
(33,'C',81,7),
(34,'D',11,12),
(35,'E',85,10),
(36,'A',39,75),
(37,'B',22,39),
(38,'C',76,67),
(39,'D',20,11),
(40,'E',81,36);


期望结果
1) N=1 取GID每组 COL2最大的记录
  +----+------+------+------+
  | id | gid | col1 | col2 |
  +----+------+------+------+
  | 6 | A | 29 | 97 |
  | 15 | E | 14 | 86 |
  | 24 | D | 54 | 79 |
  | 28 | C | 34 | 90 |
  | 32 | B | 4 | 90 |
  +----+------+------+------+
2) N=3 取GID每组 COL2最大的3条记录
  +----+------+------+------+
  | id | gid | col1 | col2 |
  +----+------+------+------+
  | 6 | A | 29 | 97 |
  | 11 | A | 2 | 78 |
  | 36 | A | 39 | 75 |
  | 32 | B | 4 | 90 |
  | 2 | B | 25 | 83 |
  | 12 | B | 30 | 79 |
  | 28 | C | 34 | 90 |
  | 23 | C | 46 | 84 |
  | 13 | C | 96 | 73 |
  | 24 | D | 54 | 79 |
  | 4 | D | 63 | 56 |
  | 9 | D | 25 | 43 |
  | 15 | E | 14 | 86 |
  | 25 | E | 85 | 64 |
  | 20 | E | 80 | 63 |
  +----+------+------+------+
   


注：
1）不限数据库，但请说明，比如 Oracle Database 10g 10.2 , MySQL 5.1.33
2) 不限方法， SQL语句，存储过程。

------解决方案--------------------
SELECT a.id,a.gid,a.col1,a.col2 FROM t2v a
LEFT JOIN t2v b
ON a.gid=b.gid AND a.col2<=b.col2
GROUP BY a.id,a.gid,a.col1,a.col2
HAVING COUNT(b.id)<=3
ORDER BY a.gid,a.col2 desc


------解决方案--------------------
SELECT a.id,a.gid,a.col1,a.col2 FROM t2v a
WHERE 3>=(
SELECT COUNT(*) FROM t2v b
WHERE a.gid=b.gid AND a.col2<=b.col2)
ORDER BY a.gid,a.col2 desc
------解决方案--------------------
恭喜版主!!

mysql:5.0.45-community-nt

1) 
select * from t2 a
where not exists
(select 1 from t2 where gid=a.gid and col2>a.col2);

2)
select * from t2 a where
3>(select count(*) from t2 where gid=a.gid and col2>a.col2)
order by a.gid,a.col2 desc;
------解决方案--------------------

SQL code

---------------------------------
--  Author: liangCK 小梁
--  Title : 查每个分组前N条记录
--  Date  : 2008-11-13 17:19:23
---------------------------------

--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (ID VARCHAR(3),GID INT,Author VARCHAR(29),Title VARCHAR(39),Date DATETIME)
INSERT INTO #T
SELECT '001',1,'邹建','深入浅出SQLServer2005开发管理与应用实例','2008-05-10' UNION ALL
SELECT '002'