Oracle - Select distinct in one column only

Question

I have a table:

aluno1 - data 1 student 1 - date2 student 2 - date1 student 2 - date2

And I want my query to return:

aluno1 - date1 (date + recent) aluno2 - date2 (date + recent)

How do I give a distinct only in the student with the latest date? I tried according to that reply, but I’m not getting the syntax right

   select *
   from (
        select ac.cod_matricula,
          he.cod_usuario_log,
          he.dt_atualiza_log,
          he.txt_ip_log,
          he.cod_usuario_log_del,
          he.dt_atualiza_log_del,
          RANK() OVER(PARTITION BY ac.cod_matricula) rnk    
        from OW.HIST_HISTORICO HE, OW.ALUNO AC
        where ac.cod_aluno_curso = he.cod_aluno_curso
        and he.cod_usuario_log = '1234567'
        --and extract(YEAR from he.dt_atualiza_log) = 2018
        order by he.dt_atualiza_log desc )
   where rnk = 1

Answer 1

As @Confuse said in the comment, you can simply use the grouping with the Max(), in that way:

    select
      ac.cod_matricula,
      he.cod_usuario_log,
      max(he.dt_atualiza_log) dt_atualiza_log,
      he.txt_ip_log,
      he.cod_usuario_log_del,
      he.dt_atualiza_log_del
    from
      OW.HIST_HISTORICO HE, OW.ALUNO AC
    where
      ac.cod_aluno_curso = he.cod_aluno_curso
      and he.cod_usuario_log = '1234567'
    group by
      ac.cod_matricula,
      he.cod_usuario_log,
      he.txt_ip_log,
      he.cod_usuario_log_del,
      he.dt_atualiza_log_del

Answer 2

Well, if you are using Oracle 12c, there is a function that has been incorporated that can help you in your situation, specifically the "FETCH FIRST n ROWS" function, in this article it is possible to see in more detail its operation and explanation of use.

Using the function for your problem, it would be something like:

select ac.cod_matricula,
       he.cod_usuario_log,
       he.dt_atualiza_log,
       he.txt_ip_log,
       he.cod_usuario_log_del,
       he.dt_atualiza_log_del
  from OW.hist_historico he, OW.aluno ac
 where ac.cod_aluno_curso = he.cod_aluno_curso
   and he.cod_usuario_log = '1234567'
 order by he.dt_atualiza_log desc fetch first 1 rows only

In case you first sort by the date you want and then ask to display n lines, in your case it would be 1.

Answer 3

Following the instructions of this answer, I believe it is possible to resolve this way:

SELECT rs.* FROM

  (SELECT
    ac.cod_matricula,
    he.cod_usuario_log,
    he.dt_atualiza_log,
    he.txt_ip_log,
    he.cod_usuario_log_del,
    he.dt_atualiza_log_del
  FROM
    OW.HIST_HISTORICO HE, OW.ALUNO AC
  WHERE
    ac.cod_aluno_curso = he.cod_aluno_curso
    and he.cod_usuario_log = '1234567') as rs

WHERE rs.dt_atualiza_log = (SELECT 
                              MAX(he.dt_atualiza_log)
                            FROM
                              OW.HIST_HISTORICO HE, OW.ALUNO AC
                            WHERE
                              ac.cod_aluno_curso = he.cod_aluno_curso
                              AND he.cod_usuario_log = '1234567'
                              AND ac.cod_matricula = rs.cod_matricula)

I didn’t have time to assemble an example dataset to test, in case you have some syntax error I believe you are simple to solve and follow the logic presented in the quoted answer. There are also presented other alternatives, brought only the simplest.