Search for registration with the highest value

[carlynhos77]

Hello, good afternoon

I set up a SELECT with the intention to seek the record that has the highest bid value, see:

Select all

select CODIGO_PROPONENTE  
      INTO :P27_COD_PROPONENTE 
      from LIC_LICITACAO_ITENS_PREGAO 
      where VALOR_LANCE = (SELECT MAX(VALOR_LANCE) FROM LIC_LICITACAO_ITENS_PREGAO 
                          where  
                          CODIGO_LICITACAO = :G_NR_LICITACAO and 
                          ID_CIDADE = :G_ID_CIDADE and 
                          LOTE = :P27_GUARDA_LOTE and 
                          CODIGO_ITEM = :P27_GUARDA_CODIGO_ITEM and 
                          MOSTRAR_TELA_LANCE = '1' 
                          ) AND  
                          CODIGO_LICITACAO = :G_NR_LICITACAO and 
                          ID_CIDADE = :G_ID_CIDADE and 
                          LOTE = :P27_GUARDA_LOTE and 
                          CODIGO_ITEM = :P27_GUARDA_CODIGO_ITEM and 
                          MOSTRAR_TELA_LANCE = '1';

Independant of how many records are selected by the criteria, I want only what has the highest value

However, I believe that it is kind of gambiarra this, because when there are 2 records with the same value , already of the error ... I also realize that sometimes the code fails to seek

in this direction there, someone has a tip that really solves it ??

[dr_gori]

But if you have 2 different proponents with the maximum value.
Which of the two do you want it to return? Whatever?

[carlynhos77]

So, for example if 3 are equal bids, I need to start with 1 ... maybe get the first in alphabetical order, or pick up the first with the smaller ID

I need to get a record , since q that is the highest value

[dr_gori]

If it does not matter what, just put at the end of your query:

Select all

AND ROWNUM = 1

there will bring only one, even if you have no maximum value.

[carlynhos77]

Wow, a piece of the code so simple kkkkkkkk ... it worked right

Thank you

[carlynhos77]

Hello, good afternoon ...

In the same idea here, however, I want to get the lowest value ... this code below I use to get the highest value and works right

Select all

select (to_char(VALOR_LANCE, 'FM999G999G999D90') - 0.01)    
      INTO :P27_VALOR_LANCE 
      from LIC_LICITACAO_ITENS_PREGAO 
      where VALOR_LANCE = (SELECT MAX(VALOR_LANCE) FROM LIC_LICITACAO_ITENS_PREGAO 
                          where  
                          CODIGO_LICITACAO = :G_NR_LICITACAO and 
                          ID_CIDADE = :G_ID_CIDADE and 
                          LOTE = :P27_GUARDA_LOTE and 
                          CODIGO_ITEM = :P27_GUARDA_CODIGO_ITEM and 
                          MOSTRAR_TELA_LANCE = '1') and 
                          CODIGO_LICITACAO = :G_NR_LICITACAO and 
                          ID_CIDADE = :G_ID_CIDADE and 
                          LOTE = :P27_GUARDA_LOTE and 
                          CODIGO_ITEM = :P27_GUARDA_CODIGO_ITEM and 
                          MOSTRAR_TELA_LANCE = '1'; 

But this same code below, I'm trying to use the min q should work ... but it does not pick up the lower value

Select all

select (to_char(VALOR_LANCE, 'FM999G999G999D90') - 0.01)    
      INTO :P27_VALOR_LANCE 
      from LIC_LICITACAO_ITENS_PREGAO 
      where VALOR_LANCE = (SELECT [color=#0000FF]MIN(VALOR_LANCE)[/color] FROM LIC_LICITACAO_ITENS_PREGAO 
                          where  
                          CODIGO_LICITACAO = :G_NR_LICITACAO and 
                          ID_CIDADE = :G_ID_CIDADE and 
                          LOTE = :P27_GUARDA_LOTE and 
                          CODIGO_ITEM = :P27_GUARDA_CODIGO_ITEM and 
                          MOSTRAR_TELA_LANCE = '1') and 
                          CODIGO_LICITACAO = :G_NR_LICITACAO and 
                          ID_CIDADE = :G_ID_CIDADE and 
                          LOTE = :P27_GUARDA_LOTE and 
                          CODIGO_ITEM = :P27_GUARDA_CODIGO_ITEM and 
                          MOSTRAR_TELA_LANCE = '1'; 

For what I researched, this min was to solve the problem, there is some detail that I am not realizing

[carlynhos77]

I think I found the point that was missing, they can only confirm if this is ... here it worked

I put the min at the beginning também, so it worked

[0]]

[dr_gori]

I have not read your response ...
When you add a code, use the

Select all

. 
 
Exemplo: 
 
[code] SELECT BLABLABLAB 

if you forget that, read here:

[carlynhos77]

Understood ... Thanks

[carlynhos77]

Hello, good morning

returning to this problem, where I need to take only the record with the highest value, I was in trouble when there are 2 larger equal values ??ne, then you told me to put at the end of the Query (and rownum = 1)

However, I realized that if the value is:

109 and another 109,05 (Query does not consider that the value of 109.05 is greater), it seems that it disregards the centavos

now, if I do not use the term (and rownum = 1) , Query can see the value of 109.05 being the largest, but if there is in the consulted group, two equal values ??of proposal, gives the error:

= # FF0000] The AJAX call returned the server error ORA-01422: Exact extraction returns more than the requested number of rows to run PL / SQL code. [/color]

[carlynhos77]

I found the problem, actually I was using the term (AND ROWNUM = 1) in the 2 SELECTS ... I checked that I need to use the term in my main SELECT, as described below:

Select all

SELECT * FROM (SELECT * FROM T_DEVEDORESCREDIT ORDER BY VALORATUAL DESC) 
WHERE ROWNUM = 1;

I did not use my sub-select.

If someone has an observation about it, please pass me ... until the moment it solved for me

Thanks