How to compare select lines with subselect output?

Asked

Viewed 177 times

3

I have a table with record of activities. So I find cases like the example below:

|codigo| etapa | - - - |abc123| 1 | |abc123| 2 | |abc123| 3 | |xyz987| 1 | |xyz987| 2 |

And I need to list the code and the last step that that code recorded. It would be the following result:

|codigo| etapa | - - - |abc123| 3 | |xyz987| 2 |

So I made the appointment:

Select codigo, max(etapa) from tabela group by codigo

So far so good. Now I need to compare this in another select. It would look something like this:

Select * from tabela where exists (Select codigo, max(etapa) from tabela group by codigo)

In this case the command ignores subselect and returns everything from the table. Someone can help me?

oracle
  • 1

    If you want to use a subselect from the same table, you need to use an alias to distinguish between the two, see if the problem isn’t that.. something like that: Select * 
from tabela t1
where exists (Select codigo, max(etapa) from tabela group by codigo) WHERE tabela.campo = t1.campo

  • In the code you put the error because you have two WHERE followed, but I put the alias correctly and the result remains the same.

  • ah yes it was an example, this Where would come before the group by within parentheses, I did not notice when I put the comment :)

  • yes yes, I threw him in and continues with the same result.

1 answer

2

Use the sub-query in select to bring the result. I added the alias to the tables so I can filter through the code I also added a Where condition to the sub-select.

Select t1.* , (select  max(t2.etapa) from tabela as t2  where t2.codigo = t1.codigo group by t2.codigo)
from tabela t1
where exists (select t2.codigo from tabela t2  where t2.codigo = t1.codigo group by t2.codigo)

  • I tried to run this code, but the first subselect shows the error "right parenthesis not found". I tried to adapt every way, but the mistake persists.

Browser other questions tagged oracle

You are not signed in. Login or sign up in order to post.