PostgreSQL - Show the last N rows for each sorted group

Question

With respect to the following posts:

Retrieving the last record in each group - MySQL

Grouped LIMIT in PostgreSQL: show the first N rows for each group?

Postgresql limit by N groups

I wrote a query to find the latest 3 entries of the last 3 groups of log events partitioned by day with a maximum of 9 total entries and I managed to gather the following data from a postgresql log table:

The query I used to get them is the following:

SELECT
    *
FROM (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY created_at::date ORDER BY created_at::time DESC) AS row_number,
        DENSE_RANK() OVER (ORDER BY created_at::date DESC) AS group_number,
        l.*
    FROM
        logs l
    WHERE
        account_id = 1) subquery
WHERE
    subquery.row_number <= 3
    AND group_number <= 3
LIMIT 9;

However I'm missing one last step: The results are "grouped" by day in descending order (which is good) but within each group the ordering by time doesn't seem to work.

Effectively the expected order should be (displaying only each row's id):

| EXISTING ROW ORDERING | EXPECTED ROW ORDERING |
| --------------------- | --------------------- |         
           52                      56
           53                      53
           56                      52
           46                      48
           48                      47 
           47                      46
           30                      30
           31                      31
           32                      32

Any ideas? Thanks.

Answer 1

If you want the data in a particular order, then you need to have an order by. SQL tables and result sets represent unordered sets. The only exception is when the outermost query has an order by.

So:

order by created_at desc