Get duplicated Ids by grouping them-CodePudding

I need to retrieve all the transactions that should have made a retry, depending on the message that the transaction received, for example, if we received an error message like "timeout" that transaction needs to make a second attempt. All the transactions are stored in a single table, meaning that if a payment id is duplicated, it means that the transaction made a retry, however, there are instances where this doesn't happen.

I wrote a query that pulls the transactions with an error message and groups them by payment id, the query works but it also brings transactions that made a second attempt.

What am I doing wrong?

   | payment_id | Bank  | time_requested | issuer      | message |attempt| 
   |   10369    | Citi  | 2021-08-03     | Capital One | success |   1   |
   |   10383    | HSBC  | 2021-07-07     | Discover    | success |   1   |
   |   84530    | HSBC  | 2021-07-07     | Visa        | timeout |   1   |
   |   84530    | HSBC  | 2021-07-07     | Visa        | success |   2   |
   |   53030    | Citi  | 2021-07-07     | Diners      | success |   1   |
   |   23930    | Citi  | 2021-07-07     | Diners      | timeout |   1   |

   select payment_id, count(payment_id) as times
   from paymentstbl
   where message in ('timeout', 'disconnected','unknown', 'Unavaialble')
   group by payment_id
   having times =1 

   Results:

   |   payment_id    | times  | 
   |    84530        | 1      |-- Wrong. Id 84530 is twice in the table
   |    23930        | 1      |-- Correct.

CodePudding user response：

It appears that you don't actually care about the count and that the actual requirement is to identify payments that were not successful. If this is true, you can do this by identifying the last row for each payment_id.

SELECT DISTINCT pt2.*
FROM paymentstbl pt1
INNER JOIN paymentstbl pt2 ON pt2.payment_id = pt1.payment_id AND
                              pt2.attempt = (SELECT MAX(attempt)
                                             FROM paymentstbl
                                             WHERE payment_id = pt1.payment_id)

Once you have the last row, you can filter them to include only those which have one of your known error messages.

WHERE pt2.message IN ('timeout', 'disconnected', 'unknown', 'Unavaialble')

Alternatively, you can exclude those which were successful.

WHERE pt2.message != 'success'

CodePudding user response：

i think, by giving or when do where clause can solve your problem.

select payment_id, count(payment_id) as times
   from paymentstbl
   where message in ('timeout', 'disconnected','unknown', 'Unavaialble') or attempt > 1
   group by payment_id
   having times =1