How do I get the latest date by following a SQL filter?

Asked

Viewed 343 times

1

I need to create a filter by following the following rule:

  • GSM which is repeated, and which MOTIVO_ENVIO equal, and STATUS = 'ANOMALIA TEMPORÁRIA' import only the line that contains DATA_ALTERACAO the most recent.

I tried to, but I couldn’t get the DATA_ALTERACAO (most recent), I even tried to use the MAX, but it didn’t work out so well, someone could help me?

select ex.gsm,
       ex.motivo_envio,
       ex.status,
       ex.data_ativacao,
       ex.data_importacao,
       ex.data_alteracao
from export ex
where ex.status = 'ANOMALIA TEMPORÁRIA'
and exists (
            select tmp.gsm
            from export tmp
            where tmp.gsm = ex.gsm
            and tmp.motivo_envio = ex.motivo_envio
            and tmp.status = ex.status
            group by tmp.gsm, tmp.motivo_envio, tmp.status
            having count(tmp.gsm) > 1)

Below is an example of what I need, GSM repeating, MOTIVO_ENVIO equal, and STATUS = 'TEMPORARY ANOMALY', I bold and underline the data I want to get, because the DATA_ALTERACAO that GSM is the latest:

Filtro para obter data mais recente

sql postgresql select filter

  • Greatly improved its edition in the previous question, but it is important to put the requirements already in one post. Asking a sequence of questions with small differences to develop your work using the features of the site is not our model. Here you already got some more answers, but you’ll really need to take a closer look at the recommended links for better use of the site in the next ones. Follow again: [Tour], [Ask], Manual on how NOT to ask questions and [Help]. - It is the recommendation to always post a [mcve] when applicable.

2 answers

1


Follow the query:

SELECT 
    ex.gsm,
    ex.motivo_envio,
    ex.status,
    ex.data_ativacao,
    ex.data_importacao,
    ex.data_alteracao
FROM
    (SELECT 
        gsm, MAX(data_alteracao) AS last_date
    FROM
        export
    GROUP BY gsm) lst
        INNER JOIN
    export ex ON lst.gsm = ex.gsm
        AND ex.data_alteracao = lst.last_date
WHERE
    ex.status = 'ANOMALIA TEMPORÁRIA'
        AND EXISTS( SELECT 
            tmp.gsm
        FROM
            export tmp
        WHERE
            tmp.gsm = ex.gsm
                AND tmp.motivo_envio = ex.motivo_envio
                AND tmp.status = ex.status
        GROUP BY tmp.gsm , tmp.motivo_envio , tmp.status
        HAVING COUNT(tmp.gsm) > 1)

I set up your environment on my server Mysql and I did the tests, with this query caught the record with the latest update date of each GSM that has the STATUS: TEMPORARY ANOMALY.

Added a sub select to verify the amount of gsm with the number in question if it is > 1 (that is, it repeats it enters the listing)

There are other ways to do it, follow the reference with some alternatives.

  • thank you very much for the reply friend, but is missing a part, you pos GSM that repeats, STATUS = 'TEMPORARY ANOMALY', DATA_ALTERACAO Major/Recent, but failed to put the MOTIVO_ENVIO equal, because can only get this result if you have this complete rule, How would I look with MOTIVO_ENVIO the same as well? like I put in Example?

  • So you want to know all the last dates for each GSM date, motive_sending, in short you want to know all the last dates for each GSM

  • No, I need to perform a filter following these steps, GSM que se repetir - MOTIVO_ENVIO que forem iguais - STATUS que forem iguais a ANOMALIA TEMPORÁRIA is to import, only will appear some repeated results, here enters the last part DATA_ALTERACAO que for a Maior/Recente take a look at this print I made: https://i.stack.Imgur.com/pVaCu.png in it is shown all these conditions, and what is to be imported is what is in Bold + Underline.

  • I’ll update see if that’s it. I need to understand the data you need from the database,

  • again missed the part where the MOTIVO_ENVIO tem que ser iguais também I believe this is what is giving problem, because it is returning me wrong data

  • Then it has to be also grouped by sending reason ?

  • Let’s go continue this discussion in chat.

Show 2 more comments

0

Make a subselect that brings the maximum date and in Where compare if the date is the last one, like this:

   select ex.gsm,
           ex.motivo_envio,
           ex.status,
           ex.data_ativacao,
           ex.data_importacao,
           ex.data_alteracao
    from export ex
    where ex.status = 'ANOMALIA TEMPORÁRIA'
    and exists (
                select tmp.gsm
                from export tmp
                where tmp.gsm = ex.gsm
                and tmp.motivo_envio = ex.motivo_envio
                and tmp.status = ex.status
                group by tmp.gsm, tmp.motivo_envio, tmp.status
                having count(tmp.gsm) > 1)
   and data_alteracao = (select max(data_alteracao) from export tmp)

  • Mano tested here, and it didn’t bring me any :/ take a look at this screenshot to see: https://prnt.sc/mm3qit

Browser other questions tagged sql postgresql select filter

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