MAX DATE - SQL ORACLE

Question

I want to select only the latest membership id from table user payments of the user with the user id equal to 1   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     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

User · Answer

Oracle 9i   maybe 8i too  has FIRST LAST aggregate functions  that make computation over groups of rows according to row s rank in group  Assuming all rows as one group  you ll get what you want without subqueries   SELECT   max MEMBSHIP ID    keep         dense rank first       order by paym date desc NULLS LAST     as LATEST MEMBER ID FROM user payment WHERE user id 1

User · Answer

select   from     SELECT MEMBSHIP ID    FROM user payment WHERE user id 1    order by paym date desc   where rownum 1

User · Answer

Try     SELECT MEMBSHIP ID   FROM user payment  WHERE user id 1  ORDER BY paym date    select MAX paym date  from user payment and user id 1     Or   SELECT MEMBSHIP ID FROM     SELECT MEMBSHIP ID  row number   over  order by paym date desc  rn       FROM user payment      WHERE user id 1   WHERE rn   1

User · Answer

SELECT p MEMBSHIP ID FROM user payments as p WHERE USER ID   1 AND PAYM DATE         SELECT MAX p2 PAYM DATE      FROM user payments as p2     WHERE p2 USER ID   p USER ID

User · Answer

Try with   select TO CHAR dates  dd MM yyy hh24 mi   from    SELECT min   TO DATE a PAYM DATE   as dates from user payment a

[sql] MAX(DATE) - SQL ORACLE

Examples related to sql

Examples related to oracle11g