MAX(DATE)-SQL ORACLE [英] MAX(DATE) - SQL ORACLE

问题描述

我只想从表user_payments中选择最新的Membership_id user_id等于1的用户的数量.

I want to select only the latest membership_id from table user_payments of the user with the user_id equal to 1.

表user_payment的外观如下:

This is how the table user_payment looks like:

   PAYM_ID    USER_ID MEMBSHIP_ID PAYM_DATE                     
---------- ---------- ----------- -------------------------------
         1          1           1 18-DEC-09 12.00.00.000000000 AM 
         2          1           2 18-DEC-10 12.00.00.000000000 AM 
         3          1           2 18-DEC-11 12.00.00.000000000 AM 
         4          2           3 17-MAR-11 12.00.00.000000000 AM 
         5          3           3 18-JUN-12 12.00.00.000000000 AM 
         6          4           2 17-FEB-12 12.00.00.000000000 AM 
         7          5           2 18-FEB-11 12.00.00.000000000 AM 
         8          5           2 18-FEB-12 12.00.00.000000000 AM 
         9          6           1 01-JUN-12 12.00.00.000000000 AM 
        10          7           1 03-FEB-11 12.00.00.000000000 AM 
        11          7           2 03-FEB-12 12.00.00.000000000 AM 

我正在尝试以下代码，但没有成功:

I am trying with no success the following code:

SELECT MEMBSHIP_ID
FROM user_payment
WHERE user_id=1 and MAX(paym_date);

我得到这个错误: SQL错误:ORA-00934:此处不允许使用组功能 00934. 00000-此处不允许使用组功能"

And I get this error: SQL Error: ORA-00934: group function is not allowed here 00934. 00000 - "group function is not allowed here"

我该如何解决?预先感谢！

How can I fix it? thanks in advance!

推荐答案

select * from 
  (SELECT MEMBSHIP_ID
   FROM user_payment WHERE user_id=1
   order by paym_date desc) 
where rownum=1;

这篇关于MAX(DATE)-SQL ORACLE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！