Oracle - Select distinct in just one column

Question

Oracle - Select distinct in just one column

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

0

I have a table:

student1 - given1 student1 - die2 student2 - given1 student2 - die2

And I want my query to return:

student1 - given1 (recent + date) student2 - given2 (recent + date)

How do I give a student distinct only with the most recent date? I tried according this answer , but I'm not hitting the syntax

   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

sql oracle

asked by anonymous 21.08.2018 / 17:44

3 answers

Display the value of the vectors in C Problem with configuring customErrors ASP.NET MVC 4

score 0 · Answer 1

As @Confundir said in the comment, you can simply use collation with Max() , like this:

    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

score 0 · Answer 2

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

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 did not have time to set up a sample dataset to test, if you have any syntax errors I think it is simple to solve and follow the logic presented in the response quoted. There are also other alternatives presented, brought only the simplest.

score 0 · Answer 3

Well, if you are using Oracle 12c, there is a built-in function that can help you in your situation, in particular the "FETCH FIRST n ROWS" function, in this article you can see more details about your operation and explanation of use.

Using the function for your problem 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 rows, which in your case would be 1.