How do I not select repeated data that contains a specific value in SQL in a column?

Asked

Viewed 202 times

0

Good evening guys, I’m beginner with Database, I have a Query that runs a filter following a rule:

Rule:

GSM which is repeated, and has in both STATUS = 'TEMPORARY ANOMALY' and its MOTIVO_ENVIO is the same, only import one record, whose DATA_ALTERACAO is more recent.

I made the filter as follows:

select ex.[Nº Tel], ex.[Nº Contrato], ex.[Motivo de Envio], ex.[Status], ex.[Data Ativação], ex.[Data Importação], ex.[Data Alteração], ex.[CPF/CNPJ], ex.[IMEI], ex.[Usuário], ex.[Filiais], ex.[Descrição Anomalia]
from Sheet1 ex
where ex.[Status] = 'ANOMALIA TEMPORÁRIA'
and exists (
            select tmp.[Nº Tel]
            from Sheet1 tmp
            where tmp.[Nº Tel] = ex.[Nº Tel]
            and tmp.[Motivo de Envio] = ex.[Motivo de Envio]
            and tmp.[Status] = ex.[Status]
            group by tmp.[Nº Tel], tmp.[Motivo de Envio], tmp.[Status]
            having count(tmp.[Nº Tel]) > 1)

I will give an example of how it is to be done:

Resultado

You can see that the camp GSM is repeating, The field MOTIVO_ENVIO is also repeating, the field STATUS is with 'TEMPORARY ANOMALY' and if repeating, that is what I need, only that out of all these records I will take what is with the DATA_ALTERACAO more recent, because that’s what I need.


But I’m having a problem, the result brings me repeated data in which some records are with the Status that contains the values ('LIBERADO', 'PENDENTE', 'EM ANÁLISE', 'ANOMALIA DEFINITIVA') and that I can’t have.

But it’s not just giving one WHERE Status NOT IN ('LIBERADO', 'PENDENTE', 'EM ANÁLISE', 'ANOMALIA DEFINITIVA') because it would only remove the record that contains this Status, I need you to remove all the Nº Tel have these Status.

Example:

     Nº Tel               Motivo de Envio                Status
_________________________________________________________________________
| 31994202248 | PRIMEIRA ATIVAÇÃO_REEMBOLSO - ADM | LIBERADO            |
| 31994202248 | PRIMEIRA ATIVAÇÃO_REEMBOLSO - ADM | ANOMALIA TEMPORÁRIA |
| 31994202248 | PRIMEIRA ATIVAÇÃO_REEMBOLSO - ADM | ANOMALIA TEMPORÁRIA |


As seen above the Nº Tel is repeating, the Motivo de Envio is equal, the Status is with 'TEMPORARY ANOMALY' only that you’re also with LIBERATED that is, I do not need these records, in case they cannot appear in the final result. I gave an example with the Status = 'LIBERADO' but the same applies to Status = PENDENTE, EM ANÁLISE, ANOMALIA DEFINITIVA

Someone?

  • Search for the DISTINCT clause: https://www.postgresql.org/docs/current/queries-select-lists.html#QUERIES-DISTINCT

  • add the distinct getting, SELECT DISTINCT (rest of query). see if it solves

  • That doesn’t solve my problem, the DISTINCT will cause that, for example if I have 5 repeated data it will bring me only one, this is not what I need, the rule I need is informed in the post with an example to better explain.

  • Be clearer about which result you want.

  • @anonimo need to create this Query to run correctly on Sqlite Nº Tel que for repetido, e tiver em ambos o STATUS = 'ANOMALIA TEMPORÁRIA' e seu MOTIVO_ENVIO for igual, importe apenas um registro, cuja DATA_ALTERACAO for mais recente. I think it makes it easier for me to tell which filter is to be done than for my attempt to do this query and try to solve its problems, surely you must have more knowledge than me at the moment in SQL, if you can help me bro, writing this query for me, 'cause I tried hard and hard, but unfortunately!

  • @anonimo I edited my question, I added more examples I was the most objective only clarifying possible, I hope you understand! you find the example here too outworking

Show 1 more comment

1 answer

1

select 
    ex.[Nº Tel],
    ex.[Nº Contrato],
    ex.[Motivo de Envio],
    ex.[Status],
    ex.[Data Ativação],
    ex.[Data Importação],
    ex.[Data Alteração],
    ex.[CPF/CNPJ],
    ex.[IMEI],
    ex.[Usuário],
    ex.[Filiais],
    ex.[Descrição Anomalia]
from Sheet1 ex
-- Inner que limita os resultados
inner join (
    select
        ex2.[Nº Tel], ex2.[Nº Contrato], max(ex2.[Data Alteração]) data_alteracao
    from Sheet1 ex2
    group by x2.[Nº Tel], ex2.[Nº Contrato]
) limite on limite.[Nº Tel] = ex.[Nº Tel] and limite.[Nº Contrato] = ex.[Nº Contrato] and limite.data_alteracao = ex.[Data Alteração]
-- FIM: Inner que limita os resultados
where ex.[Status] = 'ANOMALIA TEMPORÁRIA'
and exists (
    select tmp.[Nº Tel]
    from Sheet1 tmp
    where tmp.[Nº Tel] = ex.[Nº Tel]
        and tmp.[Motivo de Envio] = ex.[Motivo de Envio]
        and tmp.[Status] = ex.[Status]
        group by tmp.[Nº Tel], tmp.[Motivo de Envio], tmp.[Status]
        having count(tmp.[Nº Tel]) > 1
    )
  • did not work in fact 9 records that was to return me returned me 45, and if they all repeated, but even if they do not repeat will give more than 9 records.

  • See if, by chance I did not change some criteria unintentionally. Do not copy mine, make the changes on top of your SQL. Run your first and then modify, because I still think these changes can solve the problem

  • What you did differently was by the MAX and add the GROUP BY at the end, if I take this GROUP BY and leave the MAX returns me only one record, if I take the MAX and leave the GROUP BY returns me 45 equal before, then it did not give much difference, i have another Query that does this exactly but it works in Postgresql returning me the correct amount and in Sqlite it works but brings me more data too, the link is this: https://ghostbin.com/paste/9uqnn in it I make available the whole Database through a INSERT if you can help me thank you friend.

  • My dear, first I had misunderstood some details. The grouping I mentioned above wouldn’t work because I didn’t notice that there was more data that could be repeated(Like the activated data_action and the importdata_) But strangely, the answer would be what was done on the link you passed. A subselect with gsm and a data_change max, used on a Join Inner to limit the returned records. The example I did here, I reached the same conclusion, but could not test with SQL Server, only with postgresql. SQL below:

  • 1

    select ex. * from export1 ex Inner Join ( select gsm, max(data_alteration) as max_alteration from export1 group by gsm ) limits on limits.gsm = ex.gsm and ex.data_change = limits.max_change Where ex.gsm = '31994202248'

  • gsm = '31994202248' has 4 possible records, but what is in status = 'RELEASED' can be displayed by having higher date based on ghostbin data.

  • I got a little lost, what about the other rules? how Equal and equal STATUS? and you used ex.gsm = '31994202248' to test only that GSM? if I remove this part will work? I’m a little lost because I’m beginner in SQL I want to delve deeper, you could edit your REPLY by adding all fields for me to test please, and thanks for helping, was the one who continued to try to help, vlw same.

  • 1

    Yeah. This specific gsm is what I used for the test. I looked at the data you sent and looked for a GSM that had more than one occurrence to facilitate the analysis and I found this with 4 occurrences. there are others, but I needed 1 to make it easier. In the case of SQL above, it makes a subselect by taking ONLY GSM and the largest data_alteration. After that, I use this data to limit the main search and make only the last occurrence valid. See now the SQL of the answer I modified and look at her comment.

  • There was an error here, saying: no such column: ex2.Nº Tel

  • 1

    My fault, I had misspelled the SQL. Review the part of the Inner. in the reply. I just modified.

  • now ran but unfortunately continues returning me the same data that returned in the previous Query, I appreciate the help, but this p**** of Sqlite is taking me out only can, I have never seen it the same Query display in Postgresql 9 records and in Sqlite 12 records, unfortunately did not give, vlw by the attention, I’ll be taking your time no more.

  • I understand how this frustration is. Well, I didn’t know it was Sqlite so I thought I’d better test it. Using the same SQL as in https://ghostbin.com/paste/9uqnn and seems correct. I’m using to connect the https://github.com/xerial/sqlite-jdbc driver in the latest version c/ Dbeaver. I hope you get a solution.

Show 7 more comments

Browser other questions tagged

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