View most current sql server 2008 record

Question

I am trying to get the only last record (last sale) of each customer and I am not succeeding. I tried several ways, but nothing.. Only returns all sales from the date. Follow my query. I appreciate the help.

use banco
select m.cli_forn,c.nome,c.telefone,c.fax,m.num_nf,max(convert(date,m.dt_mov))as Data,m.valor_mov,m.cod_vendedor
from tb_movimentos m inner join tb_cliente c on(c.cod_cli=m.cli_forn)

where m.cod_vendedor = '178' and c.cod_cli !='5000' and status_clifor = 'C'and m.dt_mov >='2015-01-01' 
group by  m.cli_forn,c.nome,c.telefone,c.fax,m.num_nf,m.valor_mov,m.cod_vendedor

Answer 1

Here’s a classic solution:

-- código #1
USE banco;

with UltMov as (
SELECT *,
       seq= row_number() over (partition by cli_forn order by dt_mov desc)
  from tb_movimentos
  where status_clifor = 'C' 
        and dt_mov >= '20150101'
)  
SELECT C.cod_cli, C.nome, C.telefone, C.fax, 
       M.num_nf, M.dt_mov, M.valor_mov, M.cod_vendedor
  from tb_cliente as C
       inner join UltMov as M on M.cli_forn = C.cod_cli
  where M.seq = 1;

I did not test; there may be some error.