query with max value not get one row

Question

I have a table where I would like to get a row of a max value with a where. I'm trying to do:

select max(version) as max, curr_ver, creation_date, nameJob  from schema.def where nameJob = 'try'
group by curr_ver,creation_date,nameJob;

I get:

VERSION  CREATION_DATE  NAMEJOB CURR_VER
-------  -------------- -------- ---------
2        20180301       TRY      N
1        20180307       TRY      Y

I only would like to get (max row)

VERSION  CREATION_DATE  NAMEJOB CURR_VER
-------  -------------- -------- ---------
2        20180301       TRY      N

Do you know what I'm doing wrong? Thanks and sorry for my English!

Answer 1

If you want a row that has a max value of your group max values then, something like this would do:

select max(version) as max, curr_ver, creation_date, nameJob  
from schema.def 
where nameJob = 'try'
group by curr_ver,creation_date,nameJob
having 
max(version) = (select max(version) as global_max from schema.def 
where nameJob = 'try');