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!

Note to future readers: this is not a good example of a title. Please see [ask] for how to write descriptive, non-ambiguous titles. — starball, Dec 21 '22 at 22:18

score 23 · Accepted Answer · answered Jul 09 '12 at 07:52

23

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

answered Jul 09 '12 at 07:52

manurajhada

5,284
3
24
43

score 13 · Answer 2 · answered Jul 09 '12 at 07:38

13

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
)

answered Jul 09 '12 at 07:38

shawnt00

16,443
3
17
22

1

This answer correlates the subquery. You won't need to change the user_id argument in multiple places and you won't have to deal with creating multiple parameters if you use it from other code. – shawnt00 Jul 09 '12 at 07:55

score 3 · Answer 3 · answered Jul 09 '12 at 07:49

3

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

answered Jul 09 '12 at 07:49

A.B.Cade

16,735
1
37
53

+1 for suggesting the row_number() ranking function as an alternative to MAX aggregate function. Just a different way to attack the problem. – Baodad Jun 21 '13 at 20:35
same, row_number() ranking function serves me well – Ian Jan 23 '18 at 18:25

score 0 · Answer 4 · answered May 14 '13 at 00:00

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

score -1 · Answer 5 · edited Dec 07 '16 at 13:22

-1

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 )

edited Dec 07 '16 at 13:22

Matteo Baldi

5,613
10
39
51

answered Dec 07 '16 at 09:16

Dyna

9
1

MAX(DATE) - SQL ORACLE

5 Answers5

Linked