SQL - Show the top 10 values per month

Asked

Viewed 1,003 times

-2

I would like to show on the screen the 10 highest donation values of each month of the year 2018 table Doacao.

My database is SQL SERVER. For now I have the following query:

select
    datename(month,doacao.Doacao_sdt_Data) as Mes,
    pessoa.Nome_Completo, 
    doacao.valor as valor_doacao,
    CONVERT(CHAR(10),doacao.Data, 103) as data_doacao
from doacao
inner join pessoa on (doacao.codigo = pessoa.codigo)
where year(doacao.Data) = 2018
order by month(doacao.Data), doacao.valor desc

With this query I see all donations of each month, and if I put a top (10) at the beginning, appears only the first 10 January:

Below images of the result WITHOUT and WITH the Top:

Resultado SEM o TOP

Result with the TOP:

Resultado com o TOP

sql-server

  • First, you need to define whether your database is Mysql or Oracle.

  • My database is SQL SERVER.

  • (1) What is the difference between the columns Doacao_sdt_Data and Data? (2) How the column is declared Doacao_sdt_Data? (3) If the column Doacao_sdt_Data is declared as datetime, it contains date and time or date only?

  • this name is wrong Doacao_sdt_date the correct date is actually the date,

  • @Josédiz I tried to execute, but the error on this line: "seq= dense_rank over (Partition by year ([Data]), Month ([Data]) ' ------>> ERROR: >>> Msg 156, Level 15, State 1, Line 17 Incorrect syntax near the keyword 'over'.

  • I managed to fix the bug. It worked. Thanks. @Josédiz

  • @c3s1 I went to check the error and was dense_rank when the right is dense_rank(). I have also corrected in the code.

  • Exactly what I did, dense_rank() It Worked!! Thank you.

Show 3 more comments

1 answer

0


Evaluate the following suggestion, which considers that the column Data is stated as date.

-- código #1 v3
-- informe ano a consultar
declare @Ano smallint;
set @Ano= 2018;

-- variáveis de período anual (garante sargability)
declare @De date, @Ate date;
set dateformat dmy;
set @De= cast ('1/1/' + cast (@Ano as char(4)) as date);
set @Ate= cast ('31/12/' + cast (@Ano as char(4)) as date);

--
set language brazilian;

with doacao_seq as (
SELECT *,
       seq= dense_rank() over (partition by year ([Data]), month ([Data])
                             order by valor desc)
  from doacao
  where [Data] between @De and @Ate
)
SELECT datename (month, D.Data) as Mes,
       P.Nome_Completo, 
       D.valor as valor_doacao,
       convert (char(10), D.Data, 103) as data_doacao
  from doacao_seq as D
       inner join pessoa as P on (D.codigo = P.codigo)
  where D.seq <= 10
  order by month(D.Data), D.seq;

Note that 2 variables were created, @De and @Ate, to contain the selection period. The reason is described in the article "Building Efficient T-SQL Code: Sargability”.

Beyond the function dense_rank(), there is also the function rank(). There is a subtle difference between them; one should evaluate which one should be used to obtain the report. In the article "Window functions (functions window)"there is explanation of operation of these two window functions.

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.