How to Make a Scan in SQL

Asked

Viewed 169 times

1

|        cliente      |
id  | cpf  | cpf_valido
2   | 1234 |  1
3   | 1234 |  1
5   | 4567 |  0

and I need to set up the table like this

|        de_para    |
 id  | id_para | cpf 
 2   |    3    |1234
 3   |    3    |1234 
 5   |    5    |4567

To summarize, generate a scan for the cpfs with the pointing to the highest id if Cpf is validated if Cpf is invalid to maintain the same id

sql sql-server tsql

2 answers

3


Here is a suggestion for testing using the function Max with the clause Over:

select
    id,
    case when cpf_valido = 1 
        then max(id) over(partition by cpf)
        else id
    end as id_para,
    cpf
from cliente

  • Oops, it helped a lot, it left the process fast, the way I was doing it was too slow.

  • Silvio, good to see you here. Stack Overflow wins with your participation.

1

SELECT t1.id
       CASE cpf_valido
         WHEN 1 THEN (SELECT MAX(t2.id)
                        FROM tabela t2
                       WHERE t2.cpf = t1.cpf)
         ELSE t1.id
       END AS id_para,
       t1.cpf
  FROM tabela t1

Browser other questions tagged sql sql-server tsql

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