How to create a row number with specific order in postgreSQL

Question

Can anyone tell me how to get a row number like in the table below?

I have data:

• IDR (1 Month - 12 Month)

• USD (1 Month - 12 Month)

• SGD (1 Month - 12 Month)

• AUD (1 Month - 12 Month)

I want to have a row number exactly like the column "NO", where IDR goes first, then USD, SGD and AUD.

I tried using order by Currency, left(month,2), but IDR doesn't go first.

NO	Currency	Month
1	IDR	1 Month
2	IDR	3 Month
3	IDR	4 Month
4	IDR	5 Month
5	IDR	6 Month
6	IDR	12 Month
7	USD	1 Month
8	USD	3 Month
9	USD	4 Month
10	USD	5 Month
11	USD	6 Month
12	USD	12 Month
13	SGD	1 Month
14	SGD	3 Month
15	SGD	4 Month
16	SGD	5 Month
17	SGD	6 Month
18	SGD	12 Month

Answer 1

Add a computed column to the query that uses a window function with an appropriate ORDER BY clause:

SELECT row_number()
          OVER (ORDER BY CASE WHEN currency = 'IDR' THEN 1
                              WHEN currency = 'USD' THEN 2
                              WHEN currency = 'SGD' THEN 3
                              WHEN currency = 'AUD' THEN 4
                         END,
                         split_part(month, ' ', 1)::integer),
       ...
FROM ...