Max function

1

Hi everyone,

I am trying to get only a row as a result. I need the recent date.
image

Can anyone tell me where is my mistake?

2 
SELECT MAX(DATE_AFFECT)
FROM RDS_REQ_MO
WHERE NO_REQ='145583'

If you use GROUP BY it will return a row for each unique combination of values for the columns you're GROUPing.

For future reference, please post text of your query and results, rather than screenshots.

3

Since you want additional column(s) besides the max date, you should use this approach:


SELECT
    NO_REQ, CODE_EMPLOYE, DATE_AFFECT AS MAX_DATE_AFFECT
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY NO_REQ ORDER BY DATE_AFFECT DESC) AS row_num
    FROM RDS_REQ_MO
    WHERE NO_REQ='145583'
) AS derived
WHERE row_num = 1

Edit: Added DESC after the ORDER BY to get the last DATE_AFFECT; I accidentally left it off earlier.

1 Like
4

this is another way

depends on your requirement

SELECT 
     NO_REQ
  ,  MAX(DATE_AFFECT) AS MAX_DATE_AFFECT
FROM 
   RDS_REQ_MO
WHERE 
    NO_REQ='145583'
GROUP BY
       NO_REQ

image

1 Like
5

Thank you, that was the solution for my issue. I appreciate it.