How to return the second lowest date

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Thu, 18 Oct 2007 9:28 am

I have a table with code and data, where the code is repeated for different dates.
If I do the min (date), code I get the smallest data by code.
But I need the second lowest date.
At first I did a select returning any date within a select using not in and then the select main returning to second lowest date example:
Code: Select all
select min(data),codigo
from tabela
where codigo in (select codigo
                 from tabela
                 where codigo not in (select a.codigo
                                      from tabela a
                                      where data in (select min(b.data)
                                                     from tabela b
                                                     where  b.codigo =  a.codigo)
is there any simpler way to return the 2nd lowest date?????

Grateful
brauliomsf
Location: SP

Braulio Moizes

Poston Thu, 18 Oct 2007 9:59 am

try ai
Code: Select all
select  min(a.data),a.codigo
  from  tabela a
where  a.data > (select min(b.data)
                            from tabela b
                          where b.codigo = a.codigo)
rodfbar
Location: Batatais - SP

Poston Thu, 18 Oct 2007 6:50 pm

From what I understand of your query, you are returning all codes with the second smaller table date, right?

Code: Select all
select codigo
     , data
from
(
  select t.codigo
       , t.data
       , dense_rank() over (order by t.data asc) posicao
  from   tabela t
)
where  posicao = 2
;
I have no way to test this query at the moment, but I believe it works:D
rogenaro
Location: Londrina - PR

Rafael O. Genaro

Poston Mon, 05 Nov 2007 1:10 pm

kara ... I think \"ROWNUM\" também to MAKE ... TRY aí!

Code: Select all
select codigo, data
  from (select t.codigo, t.data, rownum posicao
          from tabela t
         order by t.data asc)
where posicao = 2;
falou!!
Renan Orati
Location: São José do Rio Preto - SP

Poston Mon, 05 Nov 2007 6:57 pm

kara ... I think \"ROWNUM\" also to MAKE ... TRY there!

Code: select code, data from (select t. codigo, t., rownum date position from table t order by t. date asc) where position = 2;


said!!


Just one correction: the rownum is generated before of the execution of the order by clause, so the record returned may not be the second smallest date ... You would need to add one more level in the query:
Code: Select all
select codigo, data
from 
(
  select rownum posicao, a.*
  from
  (
    select t.codigo, t.data
    from   tabela t
    order by t.data asc
  ) a
)
where posicao = 2;
rogenaro
Location: Londrina - PR

Rafael O. Genaro

Poston Thu, 06 Dec 2007 12:41 pm

personally, I have a doubt regarding the dates in a Select, and I'm posting here to create a new thread just for this: this is my inner select:
Code: Select all
SELECT Max(g.lora_dt) Ultima_Ligacao
  FROM gelogramal g, rhfunc
WHERE g.func_cd = rhfunc.func_cd
   AND g.empr_cd = rhfunc.empr_cd
   AND g.inra_ddd = '43'
   AND g.inra_nr = '33210710'
   AND g.lora_dt NOT BETWEEN To_Date('01/12/2007','DD/MM/YYYY')
                      AND To_Date('30/12/2007','DD/MM/YYYY');
she returns: ULTIMA_LIGACAO 9/10/2007 12:00 If I put it in SQL, I returns 2 records (!?)
Code: Select all
SELECT rhpessoa.pess_nm_nome,
       rhfunc.func_cd,
       gelogramal.lora_dt
  FROM rhpessoa, rhfunc, gelogramal
WHERE rhfunc.pess_cd = rhpessoa.pess_cd
   AND gelogramal.func_cd = rhfunc.func_cd
   AND gelogramal.empr_cd = rhfunc.empr_cd
   AND gelogramal.inra_ddd = '43'
   AND gelogramal.inra_nr = '33210710'
   AND gelogramal.lora_dt NOT BETWEEN To_Date('01/12/2007','DD/MM/YYYY')
                                  AND To_Date('30/12/2007','DD/MM/YYYY')

   AND gelogramal.lora_dt = (SELECT Max(g.lora_dt) Ultima_Ligacao
                          FROM gelogramal g
                         WHERE g.func_cd = rhfunc.func_cd
                           AND g.empr_cd = rhfunc.empr_cd
                           AND g.inra_ddd = gelogramal.inra_ddd
                           AND g.inra_nr = gelogramal.inra_nr
                           AND g.lora_dt NOT BETWEEN To_Date('01/12/2007','DD/MM/YYYY')
                                                       AND To_Date('30/12/2007','DD/MM/YYYY'))


      AND gelogramal.lora_bo_particular = 'S';
Code: Select all
FULANO 1   01/09/2007 10:04:00
FULANO 2   10/09/2007 12:00:00 --Deveria retornar somente esse registro! (maior data)
because this happens?!, if the inner Select returns the largest date (SELECT Max (g. lora_dt) Ultima_Ligacao), because the comparison is not working in this case?!?!?!
Porva
Location: São Paulo/SP

Rafael S. Nunes
São Paulo/SP

Poston Thu, 06 Dec 2007 2:03 pm

And there Proof, man by q I understand your main select read a table of employees, and the subquery q exists in where it is tied to the também functionary. in this way will display more than one record will be q não is that?

[] 's
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Poston Thu, 06 Dec 2007 2:55 pm

Cristiano, I removed here the comparison of employee and company and at Felix; but I have few records in this case, my fear is: I do not need to relate the Select internal with the external??? the inner select won't bring me a max (date) to another record that does not have anything to do?, I don't get the logic of the mechanism. :(
Porva
Location: São Paulo/SP

Rafael S. Nunes
São Paulo/SP

Poston Thu, 06 Dec 2007 3:06 pm

So, I didn't understand what you really need.
your query must get all employees and bring the biggest date of each or only the largest date among all??

[] 's
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Poston Thu, 06 Dec 2007 3:28 pm

the largest date among all let me try to be clearer and explain the mess: I have extensions that control works as follows, when the connection is private of the employee, the company charges for her, imagine that in the period of 9/1/2007 the 9/30/2007 the official FABIO and RAFAEL official called for a phone that is not a company contacta friend of theirs, for example whose telephone number is: 43 33334444: they called in the following sequence: Date Name date/nº tel----------------------------------------FABIO 33334444 9/10/2007 43 43 9/1/2007 RAFAEL 33334444 it when the user asks for a filter of links of December/2007, again I check if there are records of this external phone (43 33334444) OUT of the period informed and with related already employees *looking for occurrences of employees who are already related to that number, aí on the block I would bring this clerk tabular with a different color suggesting that number belongs to him, what I want is to bring an employee only, in this case, the last one I called this number.

dae I thought about doing a Select to return the last call made to phone (43 33334444) and that is with the funcioário already connected in the back, in the month of September the Chief of the Department lists the employee as follows, the records are displayed in a block multirecord, and passes a list on paper, by the Department for each clerk at identify your calls, aí the user (in the system) ties these calls to employees.
Porva
Location: São Paulo/SP

Rafael S. Nunes
São Paulo/SP

Poston Thu, 06 Dec 2007 3:43 pm

look aí Cristiano, to be more visual it it when I ask for the range of December (01 to 31), he again will have this phone ' 33210710 ', dae I bring automatically the last official who called him, in this case, myself in..

[img]http%20://img523%20.%20imageshack%20us/.%20img523/5052/ramaisez8%20.%20jpg[/img]
Porva
Location: São Paulo/SP

Rafael S. Nunes
São Paulo/SP

Poston Thu, 06 Dec 2007 5:27 pm

And there Proof, beauty?
Dude, take a look at this code, see if it's +-

Code: Select all
SQL> select * from mens_erro;

NOME                                     DATA      TEL
---------------------------------------- --------- ---------------
Fabio                                    01-SEP-07 43 33334444
Rafael                                   10-SEP-07 43 33334444

SQL> SELECT a.*, nvl2(b.maxdata,'Ultimo',null) flag
  2    FROM mens_erro a,
  3         (SELECT MAX (DATA) maxdata
  4            FROM mens_erro) b
  5   WHERE a.DATA BETWEEN TO_DATE ('01/09/2007', 'DD/MM/YYYY')
  6                    AND TO_DATE ('30/09/2007', 'DD/MM/YYYY')
  7     AND a.data = b.maxdata(+)
  8  /

NOME                                     DATA      TEL             FLAG
---------------------------------------- --------- --------------- ------
Rafael                                   10-SEP-07 43 33334444     Ultimo
Fabio                                    01-SEP-07 43 33334444


[]'s
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Poston Fri, 07 Dec 2007 7:57 am

Cristiano, I was with the hot head yesterday and wasn't able to think straight!, I made it simple even, like, I was relating internal consultation with the employee, and was bringing in more than one record so I just did the two practically identical queries, but the intern with MAX and relating in own consultation with employee:
Code: Select all
SELECT rhpessoa.pess_nm_nome,
       rhfunc.func_cd,
       gelogramal.lora_dt
  FROM rhpessoa, rhfunc, gelogramal
WHERE rhfunc.pess_cd = rhpessoa.pess_cd
   AND gelogramal.func_cd = rhfunc.func_cd
   AND gelogramal.empr_cd = rhfunc.empr_cd
   AND gelogramal.lora_dt = (  SELECT Max(g.lora_dt)
                                 FROM gelogramal g, rhfunc f --relacionei internamente RHFUNC
                                WHERE g.func_cd  = f.func_cd
                                  AND g.empr_cd  = f.empr_cd
                                  AND g.inra_ddd = '43'
                                  AND g.inra_nr  = '33210710'
                                  AND g.lora_bo_particular = 'S'
                                  AND g.lora_dt NOT BETWEEN TO_DATE('01/12/2007','DD/MM/YYYY')
                                                        AND TO_DATE('30/12/2007','DD/MM/YYYY') );
now a curiosityas Oracle proceeds in this case, it executes the inner query first????
Porva
Location: São Paulo/SP

Rafael S. Nunes
São Paulo/SP

Poston Fri, 07 Dec 2007 8:14 am

So Proof, beleza guy?

Following, I think, is not that he READ first the internal but, I believe it to boot the Parse, from bottom to top.

Correct Me if I'm wrong.
Trevisolli
Location: Araraquara - SP

Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Poston Fri, 07 Dec 2007 8:18 am

And there Proof, right?

Guy, you put in the subquery table rhfunc, she is relating with gelogramal, but this relationship I think unnecessary because you're not making any filter in rhfunc, and também is not using any field with the outer query ....
Now you have a doubt about this excerpt here.
Code: Select all
... AND gelogramal.lora_dt = (  SELECT Max(g.lora_dt) ...
doing that way you will only bring the ultimate user who made the link!!!???... is that right? or would have q be an outer join at that point.

[] 's
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Poston Fri, 07 Dec 2007 8:29 am

puts, it is true man, traveled again!

and Yes, I just need to bring 1 employee, the last one called ' MAX (date) ' so that you're fucking out, cuz if I relacino the inner query with the external, aí falls in that case to bring more than 1 employee, and that can't happen forget my SQL aí's post above is wrong: roll:
Porva
Location: São Paulo/SP

Rafael S. Nunes
São Paulo/SP

Poston Fri, 07 Dec 2007 8:53 am

I even tried a:
Code: Select all
SELECT rhpessoa.pess_nm_nome,
       rhfunc.func_cd,
       gelogramal.lora_dt
  FROM rhpessoa, rhfunc, gelogramal
WHERE rhfunc.pess_cd = rhpessoa.pess_cd
   AND gelogramal.func_cd = rhfunc.func_cd
   AND gelogramal.empr_cd = rhfunc.empr_cd
   AND gelogramal.inra_ddd = '43'
   AND gelogramal.inra_nr  = '33210710'
   AND gelogramal.lora_bo_particular = 'S'
   AND gelogramal.lora_dt NOT BETWEEN To_Date('01/12/2007','DD/MM/YYYY')
                                  AND To_Date('30/12/2007','DD/MM/YYYY')
   AND ROWNUM < 2
ORDER BY gelogramal.lora_dt DESC;
but it executes the ROWNUM before the ORDER BY, aí stays the same!
Porva
Location: São Paulo/SP

Rafael S. Nunes
São Paulo/SP

Poston Fri, 07 Dec 2007 10:24 am

solved here Cristiano, I just took the inner query's relationship with the external, thanks for your patience aí (y)
Porva
Location: São Paulo/SP

Rafael S. Nunes
São Paulo/SP

Poston Fri, 07 Dec 2007 10:32 am

Cool dude, sorry it took so long to respond, I'm OK here wrapped hj q. hehehehehe [] 's!!!
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Poston Fri, 07 Dec 2007 12:15 pm

the loco meo, this, hehe, you was helpful, thanks once again asked for a guy bozão of SQL that works here but I was on vacation I thought:) should have this connection between the two select statements, but not: S
Porva
Location: São Paulo/SP

Rafael S. Nunes
São Paulo/SP


  • See also
    Replies
    Views
    Last Post

      Next

      Return to SQL

      Who is online

      Users browsing this forum: Google [Bot] and 6 guests