Get last value in sequence-CodePudding

I am trying to return the last value in a sequence of a particular event, my inital thought was to use LAST_VALUE() but I can't get this to work. I could do this with subqueries and joins however is there a window function that would give this result far easier?

Right now the query is pulling the max amount but what I want is the last amount based on the seq column

SQL Fiddle

Data

| PaymentID |          Description | Result | Seq |
|-----------|----------------------|--------|-----|
|         1 | Entered Payment Page |    Yes |   1 |
|         1 |       Amount Entered |     50 |   2 |
|         1 |       Amount Entered |     60 |   3 |
|         1 |       Amount Entered |     20 |   4 |
|         1 |     Amount Confirmed |    Yes |   5 |
|         2 | Entered Payment Page |    Yes |   1 |
|         2 |       Amount Entered |    100 |   2 |
|         2 |     Amount Confirmed |    Yes |   3 |
|         3 | Entered Payment Page |    Yes |   1 |
|         3 |       Amount Entered |      4 |   2 |
|         3 |     Amount Confirmed |     No |   3 |
|         3 |       Amount Entered |      8 |   4 |
|         3 |     Amount Confirmed |    Yes |   5 |

Current Query Result

| PaymentID | InPayment | Amount | Confirmed |
|-----------|-----------|--------|-----------|
|         1 |       Yes |     60 |       Yes |
|         2 |       Yes |    100 |       Yes |
|         3 |       Yes |      8 |       Yes |

Desired result

| PaymentID | InPayment | Amount | Confirmed |
|-----------|-----------|--------|-----------|
|         1 |       Yes |     20 |       Yes |
|         2 |       Yes |    100 |       Yes |
|         3 |       Yes |      8 |       Yes |

CodePudding user response：

You can use row_number() and conditional aggregation:

select paymentid,
       max(case when description = 'Entered Payment Page' then result end) as inpayment,
       max(case when description = 'Amount Entered' then result end) as amount_entered,
       max(case when description = 'Amount Confirmed' then result end) as amount_confirmed
from (select t.*,
             row_number() over (partition by paymentid, description order by seq desc) as seqnum
      from paymentinfo t
     ) t
where seqnum = 1
group by paymentid;

Here is a SQL Fiddle.