【Oracle】RANK() 及 PARTITION

取出當日交易筆數超過 3 筆之客戶的前 3 筆交易

SELECT * FROM
(
SELECT member_id, trs_time, RANK() OVER (PARTITION by member_id ORDER BY trs_time) trs_rank FROM table1
WHERE trs_date='20090901' AND member_id IN (SELECT DISTINCT member_id FROM table1 WHERE trs_date='20090901' GROUP BY member_id HAVING COUNT(*)>2)
)
WHERE trs_rank<4

====================================
PostgresSQL 要 8.4, MSSQL2005 才有同樣的功能.

====================================
MySQL :

SET @rank=0;

SELECT @rank:=@rank+1 AS rank, id, name, subject FROM mca ORDER BY id DESC;