As the second lowest return date

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
brauliomsf
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 9
Joined: Thu, 18 Oct 2007 9:18 am
Location: SP
Braulio Moizes

I have a table with code and date, where the code repeats itself for distinct dates.
If I do the min (date), code I get the lowest date by code.
But I need the second smaller date.
At the beginning I did a select returning the smallest date within a select using not in and following the main SELECT returning the 2nd lower date:

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 to the 2nd lower date ?????

grateful
rodfbar
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 241
Joined: Tue, 09 Oct 2007 11:15 am
Location: Batatais - SP

Try there

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)
rogenaro
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Fri, 30 Mar 2007 7:26 pm
Location: Londrina - PR
Rafael O. Genaro

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

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 can not test this query at the moment, but I believe it works: D
Renan Orati
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 90
Joined: Thu, 23 Aug 2007 3:40 pm
Location: São José do Rio Preto - SP

kara ... I think the "rownum" também to do ... try to!

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 !!
rogenaro
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Fri, 30 Mar 2007 7:26 pm
Location: Londrina - PR
Rafael O. Genaro

Kara ... I think the "Rownum" also can do ... Try it!

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;  
Spoke !!
A correction:
The rownum is generated before of the execution of the Order BY clause, so the returned record may not be the second smaller date ... it would be necessary to add another level in the query:

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;
User avatar
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 342
Joined: Mon, 29 Jan 2007 7:36 am
Location: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

Guys, I have a question regarding dates in a select, and I'm posting here to not create a new topic just for this:

This will be my internal SELECT:

0]]

It returns me:

Ultima_ligation
10/09/2007 12:00:00

99] If I put it in this SQL, return me 2 records (!?)

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';

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) 
: It happens?!, now, if the internal select returns the largest date (SELECT MAX (G.LORA_DT) Ultima_ligacao), because the comparison is not working in this case?!?!?!?!
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

And there,

face by what I understood your main select reads a table of officials, and the subquery that exists in her wher is tied to the official tb .. this way more than one Registration .... Is it not that?

[] 's
User avatar
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 342
Joined: Mon, 29 Jan 2007 7:36 am
Location: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

Cristiano, I took off the comparison of employee and company and worked, but I have few records in this case, my fear is: I do not need to relate the internal SELECT with the external ??? The internal Select will not bring me a Max (date) from another record that has nothing to see?, I did not well out the logic of the mechanism! :(
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

So, I do not understand what you really need ..
This your query should fetch all employees and bring the largest date of each or only the largest date among all ??

[] 's
User avatar
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 342
Joined: Mon, 29 Jan 2007 7:36 am
Location: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

The largest date between all

Let me try to be clearer and explain the mess:


I have the control of extensions that works Next form, when the connection is particular from the employee, the company charges for her, imagine that in the period from 01/09/2007 to 09/30/2007 the employee Fabio and the employee Rafael called a phone that is not a contact from the company, a friend of them for example whose phone number is: 43
33334444:

They linked the following date / sequence:

Select all

Nome    Data           Nº tel 
---------------------------------------- 
FABIO   01/09/2007     43 33334444 
RAFAEL  10/09/2007     43 33334444

When the user requests a filter of the month of December / 2007, again I check

if there are records of this external telephone (43 33334444) outside of the informed period and with employees already related *, looking for employees who are already related to that number, and in the tabular block I would already bring this employee with a different color suggesting that that number belongs to him, what I want is r An employee only, in this case, the last that called that number.

Dae I thought of doing a Select that returns the last call to the phone (433334444) and that is with the capitio already related back there in September


* The head of the Deptant relates the employee as follows, the records are displayed in a multirecord block, and passes a list on the same paper, by the DEPT for each function to identify his connections, and the user ( In the system) ties these links to employees.
User avatar
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 342
Joined: Mon, 29 Jan 2007 7:36 am
Location: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

Look at Cristiano, to stay more visual the thing

when I ask for the December interval (01 to 31), he will again have this phone '33210710', DAE I automatically bring the last employee who called him, in case, myself in the example ..

[IMG http://img523.imageshack.us/img523/5052/ramaisez8.jpg[/img]
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

And there, beauty, beauty ??
face, take a look at this code, see if it is + - this ..

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
User avatar
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 342
Joined: Mon, 29 Jan 2007 7:36 am
Location: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

Cristiano, I had a hot head yesterday and I was not able to think right! I did it rightly, like, like I was relating an internal consultation employee with the external, and I was bringing more than one record so I just did The two practically identical consultations, but the internal with MAX and relating to the employee's own consultation:

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 curiosity, such as the Oracle proceeds in this case, does it run the internal consultation first ????
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

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

So pore, beleza guy?

Next, I believe that, it is not that he reads the inner first but, I believe he boots the parse, from the bottom up.

Correct me if I'm wrong.
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

And there, beauty?

face, in the subquery you put the RHFUNC table, she is relating to the jellogramal, but this relationship I think unnecessary because you are not doing any filters in rhfunc, and tb is not using any Field of her with the external query ....
Now get a doubt, on this stretch here.

Select all

 
... AND gelogramal.lora_dt = (  SELECT Max(g.lora_dt) ... 
doing this way you will only bring the last user who made the call !!! ??? ... that's right? or would have to be an outer join at this point.

[] 's
User avatar
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 342
Joined: Mon, 29 Jan 2007 7:36 am
Location: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

Puts, it's true, man, I traveled again!

and yes, I just need to bring 1 employee itself, the last that called 'max (date)'

then, that's fucking fuck, Because if I relatin the internal consultation with the external, and it falls in that case of bringing more than 1 employee, and this can not happen

forget my SQL aí from the post above, it's wrong: roll:
User avatar
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 342
Joined: Mon, 29 Jan 2007 7:36 am
Location: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

I even tried a:

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 he performs the rownum before Order By, and it is in the same!
User avatar
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 342
Joined: Mon, 29 Jan 2007 7:36 am
Location: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

I decided here Cristiano, I only took the relationship of the internal consultation with the external, thanks for the patience aí (y)
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

Legal guy, sorry for the delay to answer, it's q hj I'm well curled here. Hehehehehe

[] 's !!!
User avatar
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 342
Joined: Mon, 29 Jan 2007 7:36 am
Location: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

The Loco Meo, that this, hehe, you were helpful for damn, thanks once again

I asked Prum Face Bozão do SQL that works here but who was on vacation :) [[99]
I thought I should have this connection between the two SELECTS, but not: S
markosoftware
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Posts: 1
Joined: Sat, 14 Sep 2013 2:36 pm

Good night ,,, cara I do not know if I bored very well ... more follows an example with a very simple logic of how to return the second largest date or vlr,



Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 15 guests