Create a select within another select to select the top ten values of each month

Question

I’m trying to get the 10 best values of each month, I’ve looked for article here, but none helped me so far, I tried to do several subquery, but it was almost the same thing to take table 1 and combine each value with itself. You could do something like: SELECT (SELECT NOME, TOTAL FROM TB1 WHERE MES = '2016-01-01') AS 'JAN',(SELECT NOME, TOTAL FROM TB1 WHERE MES = '2016-02-01') AS 'FEV' ... (SELECT NOME, TOTAL FROM TB1 WHERE MES = '2016-12-01') AS 'DEZ' FROM TB1?

Answer 1

Assuming you have a table with the following structure:

+-------+----------+
| CAMPO | TIPO     |
+-------+----------+
| ID    | NUMERIC  |
| NOME  | VARCHAR  |
| TOTAL | NUMERIC  |
| MES   | DATE     |
+-------+----------+  

You could do something like:

WITH DADOS AS (
    SELECT ID, 
           NOME,
           TOTAL,
           EXTRACT(MONTH FROM MES) MES,
           EXTRACT(YEAR FROM MES) ANO,
           RANK() OVER (PARTITION BY EXTRACT(YEAR_MONTH FROM MES) ORDER BY TOTAL DESC) RANKING
    FROM TABELA1
)
SELECT ID,
       NOME, 
       TOTAL,
       MES,
       ANO
FROM DADOS
WHERE RANKING < 11

Explanation

In this case we build a relationship using the WITH, in this relationship we have the ID, NOME, value TOTAL, MES, ANO and RANKING.

To build the RANKING we use the function RANK, and specify a partition, within a partition the count is restarted for each partition, in our case we use the ANO_MES as a partition and sort the values in a decreasing way (from the highest to the lowest).

This gives us a relationship that stays with this structure.

+-------+----------+
| CAMPO | TIPO     |
+-------+----------+
| ID    | NUMERIC  |
| NOME  | VARCHAR  |
| TOTAL | NUMERIC  |
| MES   | NUMERIC  |
| ANO   | NUMERIC  |
| RANK  | NUMERIC  |
+-------+----------+  

Once armed with this relationship we only made one select, limiting RANK to the first 10.

How do we have a column for the MES and another to the ANO you can do a filtering with ease.

Considerations.

This approach does not return each month as a column, so if you want to do this, one possibility is to select several times the data relation, one for each month and filtering the month of each in the clause WHERE.