Get a result that appears in 1st position if you have a value specified in SQL

Asked

Viewed 148 times

3

I need to create a query that follows some rules and they are:

  1. gsm repeat

  2. sending be different

  3. status who is with ANOMALY and appears as the first result (over the others)


I’ll give you an example of how the data appears, and what data I need to get(*):

  gsm   motivo_envio     status     data_ativacao
_________________________________________________
| 111 |     aaa      |  LIBERADO   | 02/01/2019 |
| 111 |     aaa      |  ANOMALIA   | 01/01/2019 |
| 111 |     bbb      |  PENDENTE   | 25/12/2018 |
-------------------------------------------------
| 222 |     aaa      |  ANOMALIA   | 02/01/2019 | *
| 222 |     bbb      |  PENDENTE   | 01/01/2019 |
-------------------------------------------------
| 333 |     aaa      |  ANOMALIA   | 02/02/2019 | *
| 333 |     bbb      |  ANOMALIA   | 01/03/2019 | *
-------------------------------------------------
| 444 |     aaa      |  DEFINITIVA | 02/01/2019 |
| 444 |     bbb      |  ANOMALIA   | 01/01/2019 | *
-------------------------------------------------
| 555 |     bbb      |  LIBERADO   | 02/01/2019 |
| 555 |     aaa      |  ANOMALIA   | 22/04/2019 | *
| 555 |     bbb      |  ANALISE    | 02/01/2019 |
-------------------------------------------------
| 666 |     aaa      |  LIBERADO   | 02/01/2019 |
| 666 |     bbb      |  ANOMALIA   | 02/01/2019 | *
| 666 |     ccc      |  ANOMALIA   | 04/01/2019 | *
| 666 |     ccc      |  PENDENTE   | 07/01/2019 |
| 666 |     ddd      |  ANOMALIA   | 02/01/2019 | *
-------------------------------------------------

I will explain the results:

The numbers 111 were not taken because these data only follow the 1st and 2nd rule gsm repeating sending that is different, but the status of ANOMALY is the last who is on top is the LIBERATED.

The numbers 222 were only taken with ANOMALY because follow the filters, gsm repeating sending that is different and status with ANOMALY and is on top.

The numbers 333 were taken both, because they follow the filters and both have only one record in each sending what put them in first place or is over.

The numbers 444 got what you’re holding ANOMALY because they follow the filters.

The numbers 555 were picked up what you’re with ANOMALY because they follow the filters.

The numbers 666 were taken 3 records being that the sending bbb appears once and is with ANOMALY and the sending ccc appear 2 times being that the first place is the status with ANOMALY and the sending ddd also appears once and is with ANOMALY.


I tried to create a query to get these expected results but the query brings unwanted results:

SELECT DISTINCT a.gsm, a.motivo_envio, a.status
FROM export b
INNER JOIN ( SELECT * FROM export a WHERE a.status = 'ANOMALIA' ) AS a ON a.gsm = b.gsm
WHERE 
      ( 
        b.status = 'PENDENTE'  
        OR b.status = 'LIBERADO'
        OR b.status = 'ANALISE'
        OR b.status = 'DEFINITIVA'
      ) 
AND b.motivo_envio <> a.motivo_envio

I created a database online to test

The Insert of the bank is here

Could someone help me?

sql postgresql query filter

  • What exactly do you mean by "(over others)"? What sort is it considering since, by definition, the relational model does not present an implicit ordering.

  • is still a little confused, can put together a list only with the results of how you expect it to return?

  • @Ricardopunctual I put the results I need to get in the example marked with a * but here is the list of those results I need to get: https://pastebin.com/Xxec9ycq

  • @anonimo so bro, this "over the others" is just a way of expressing, to say that I need the ANOMALY to be appearing first, for example if you shudder the numbers 111 they were not caught because these data only follow the 1st and 2nd rule, but the status of ANOMALY is last on top is the RELEASED, or could only have taken if the ANOMALY had over. I tried to explain it a little bit better just below the example, if you can help me bro, I really need it and I’m not getting it.

  • 1

    And being "on top of others" means that the status is of the latest (or oldest) date? You have to state what the sort criteria are to define the "be on top".

  • @anonimo as this was an example only for demonstration even, the spreadsheet where I take this data has many fields and 3 of them are for Dates, and the method of sorting them is the most recent date even, ie the record that has above will have the most recent date.

  • Then take the MAX(your data) and check if it is ANOMALY.

  • @anonimo I’m beginner with databases once I tried to use MAX in date and it didn’t work very well, can you write this query so I can test? I have an online database just to write the query: https://www.db-fiddle.com/f/juHY8NoPG3oSEXmSNCSQ/0 if you can thank me, I really need to make it work.

  • 1 and 2 are common conditions, and 3 is basically that How to select with ORDER BY and different criteria?

  • @Bacco I’m having trouble creating, not working out, would be able to give me an example of this 3rd part of the filter?

  • Yes, there’s no link

  • Kind of thing I would do by FUNCTION given the complexity of the rule.

  • @Motta I don’t know how to create Function yet :/ I’m a beginner with BD, I needed to develop this filter, but I’m not able to finish it

  • @Bacco did this query: select a.* from export a Where a.status = 'ANOMALY' and exists ( select b.gsm from export b Where b.gsm = a.gsm ) E is picking up everything I need, only it’s picking up the 111 with ANOMALY and it wasn’t meant to pick up, because the status RELEASED is over the ANOMALY, I tried to perform the ordering as sent me the link but it did not work like this: ... order by a.status = 'ANOMALY' does not return an error, but does not change the result at all.

  • @Magno: It is impossible in SQL, particularly in Postgresql, for you to guarantee an order of the retrieved rows from a table without using the ORDER BY clause. In the table you placed in the fiddle you do not have the fields necessary for such ordering.

  • @anonimo I edited the question added the Date field, and changed the online database in Fiddle with this new data.

  • @anonymimo here’s a print of my data source that’s where I send the data to the database and run the querys, is a spreadsheet that has several fields and two fields that I believe serve for comparison would be the NUM which is a field that assigns a sequential number and the Dates column: https://prnt.sc/odvrth

Show 12 more comments

1 answer

4


@Magno, I’ve made some adjustments, see if that solves your doubt.

https://www.db-fiddle.com/f/iDMiXohGAFLqxBG2bm2HvX/0

Note that one of the cases will not return as expected (the 666), since the date of the PENDING type is greater than that of the ANOMALY. The insertion position is not what will determine the record, but the date the query is ordering.

with cte as (
    select gsm, motivo_envio, status, to_char(data_ativacao, 'DD/MM/YYYY') as data_ativacao,
    rank () OVER (Partition by gsm,motivo_envio order by data_ativacao desc) as ranking
    from export
)
select * from cte
where status = 'ANOMALIA' and ranking = 1

  • Thank you very much for the attention there in the SQL Server group on Telegram, and for the willingness to help me, that’s what I needed, literally you understood the results I needed to get, thank you very much!

Browser other questions tagged sql postgresql query filter

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