I'm new to SQL programming, and im wondering how can one go about getting the latest row based on a criteria that needs looping. In VBA, it's just a simple do loop until, but in SQL i looked thru recursive CTE and i tried subqueries but i dont think the database itself supports it. I'm using PowerQuery from Excel to connect to the database with SQL
+--------+--------+--------+
| Status | Date | Amount |
+--------+--------+--------+
| n | 18-Mar | -50 |
| c | 17-Mar | 50 |
| n | 16-Mar | -50 |
| c | 15-Mar | 50 |
| n | 14-Mar | -50 |
| c | 13-Mar | 50 |
| c | 12-Mar | 50 |
+--------+--------+--------+
The result i'm trying to get is the date 12-MAR. Basically, the coding logic is to get the latest status 'C' without an 'N' after it.
select status,max(date) 'Max Date',amount
from table A
where status <> 'n' and
-- where i dont know how to make it check to see if there's a status 'n' after max date and if so, loop back until it finds a status 'C' without having a status 'N' after --
group by status
Many thanks in advance for your help!