分组记录第一条（转）

1 --建立测试环境   
CREATE   TABLE   table1(a   VARCHAR(10),b   VARCHAR(10),c   VARCHAR(10));   

--插入数据   
INSERT   INTO table1 VALUES('2004','12','storea');

INSERT   INTO   table1 VALUES('2005','07','storea');   

INSERT   INTO   table1 VALUES('2004','11','storeb'); 

INSERT   INTO   table1 VALUES('2005','06','storeb');  
  
COMMIT;

2 取分组记录的第一条

解法一

SELECT   a.*   FROM   table1   a   INNER   JOIN   (   
SELECT   MAX(a   +   b)TIME   FROM   table1     
GROUP   BY   c   )   b   
ON   a.a   +   a.b   =   b.TIME

解法二

SELECT * FROM scott.table1 a WHERE NOT EXISTS
   (SELECT 1   FROM scott.table1 b WHERE a.a=b.a AND TO_NUMBER(a.b)<TO_NUMBER(b.b));

解法三

SELECT * FROM (SELECT t.*, RANK() 
        OVER (PARTITION BY t.a ORDER BY t.b DESC) AS drank
FROM table1 t) a WHERE drank=1

引申

SELECT t.*,SUM(b) OVER (PARTITION BY t.a) AS aaa,
   SUM(b) OVER () AS bbb
FROM table1 t
ORDER BY t.a,t.b

 

 

－－－－－－

SELECT d.department_id , e.last_name, e.salary, RANK() 
        OVER (PARTITION BY e.department_id ORDER BY e.salary) AS drank
FROM employees e, departments d
WHERE e.department_id = d.department_id
   AND d.department_id IN ('60', '90');

http://www.cnblogs.com/ball-head/archive/2010/01/20/1652822.html