column must appear in the GROUP BY clause

[szzfl]

mysql下：
mysql> select * from temp;
+------+--------+------------+
| id | amount | date |
+------+--------+------------+
| 1 | 10 | 2013-10-11 |
| 2 | 30 | 2013-11-11 |
| 3 | 5 | 2013-12-14 |
| 1 | 15 | 2013-12-10 |
| 3 | 1 | 2013-12-25 |
+------+--------+------------+
取每个id最近一天的amount。

mysql> select *from (select * from temp order by date desc) temp group by id;
+------+--------+------------+
| id | amount | date |
+------+--------+------------+
| 1 | 15 | 2013-12-10 |
| 2 | 30 | 2013-11-11 |
| 3 | 1 | 2013-12-25 |
+------+--------+------------+

在pgsql或者标准sql中，将会报错：
ERROR: column xxxx must appear in the GROUP BY clause or be used in an aggregate function

请问类似功能在标准sql中如何写？

[szzfl]

有没有人知道啊？

[ACMAIN_CHM]

参考下贴中的多种方法 http://blog.csdn.net/acmain_chm/article/details/4126306 [征集]分组取最大N条记录方法征集，及散分....

[懒得去死]

给你个例子，对着改下。

t_girl=# select * from inc_test;
 id |   l_date   
----+------------
  3 | 2014-01-25
  3 | 2014-01-29
  1 | 2014-02-01
  1 | 2014-02-11
  1 | 2014-02-05
  2 | 2014-02-10
  2 | 2014-01-31
(7 rows)

t_girl=# select id,l_date from (select id,l_date, row_number() over(partition by id order by l_date desc ) as rank from inc_test) as a where a.rank = 1;
 id |   l_date   
----+------------
  1 | 2014-02-11
  2 | 2014-02-10
  3 | 2014-01-29
(3 rows)

[ghostkngiht]

select id, SUM(amount), MAX(date) from temp group by id;

[DB_tester]

postgres=# select * from temp where date in (select max(date) from temp group by id); id | amount | date ----+--------+------------ 2 | 30 | 2013-11-11 1 | 15 | 2013-12-10 3 | 1 | 2013-12-25 (3 行记录) 使用了你的前5条记录作为例子