How to get first rows from resultset in sql server

Transact-SQL
1

Hi all
i have the table like this

12411 1 622 2016-03-14 00:00:00 730.24 ****
12411 1 622 2016-03-01 00:00:00 586.35
12411 1 622 2016-02-24 00:00:00 1554.22
12411 1 622 2016-02-22 00:00:00 1342.07
12411 1 622 2016-02-10 00:00:00 1316.59
12411 1 622 2016-02-02 00:00:00 767.92
12411 1 622 2016-01-19 00:00:00 1262.48
12442 1 650 2015-05-21 00:00:00 3607.50 *****
12442 1 650 2014-09-22 00:00:00 3069.60
12443 1 637 2015-07-30 00:00:00 268.32 *****
12447 1 668 2015-08-17 00:00:00 1046.08 ****
12447 1 668 2015-03-30 00:00:00 391.05
12447 1 668 2015-03-13 00:00:00 1358.28

this is a result set from

SELECT
cli_codigo
,cli1_Empresa
,cli1_vend
,dup_dtemissao
,dup_vlduplicata

FROM teste.dbo.Clientes
LEFT OUTER JOIN teste.dbo.duplicat D ON Clientes.cli_codigo = D.dup_codigo
LEFT OUTER JOIN teste.dbo.CLIENTES1 ON Clientes.cli_codigo = Clientes1.cli1_codigo

where
cli_codigo between 12411 and 13000

order by cli_codigo , dup_dtemissao desc

I would like to get the rows marked as "****" , that is
max(date) and his value not the max(value)

any help will appreciated
tks
Clages

2

Use ROW_NUMBER function - like this:

;WITH cte AS
(
	SELECT  cli_codigo ,
			cli1_Empresa ,
			cli1_vend ,
			dup_dtemissao ,
			dup_vlduplicata,
			ROW_NUMBER() OVER (PARTITION BY cli_codigo ORDER BY dup_dtemissao DESC) AS RN
	FROM    teste.dbo.Clientes
			LEFT OUTER JOIN teste.dbo.duplicat D ON Clientes.cli_codigo = D.dup_codigo
			LEFT OUTER JOIN teste.dbo.CLIENTES1 ON Clientes.cli_codigo = Clientes1.cli1_codigo
	WHERE   cli_codigo BETWEEN 12411 AND 13000
)
SELECT
	cli_codigo ,
	cli1_Empresa ,
	cli1_vend ,
	dup_dtemissao ,
	dup_vlduplicata	
FROM
	cte
WHERE
	RN = 1
ORDER BY
	cli_codigo ,
    dup_dtemissao DESC;
3

I did this way
looks like yours
tks
carlos Lages

select
cli_codigo ,cli1_Empresa ,cli1_vend,dup_dtemissao , dup_vlduplicata , row from
(
SELECT
cli_codigo
,cli1_Empresa
,cli1_vend
,dup_dtemissao
, dup_vlduplicata
,ROW_NUMBER() OVER(PARTITION BY cli1_empresa,cli_codigo ORDER BY dup_dtemissao desc ) AS Row
FROM teste.dbo.Clientes
LEFT OUTER JOIN teste.dbo.duplicat D ON Clientes.cli_codigo = D.dup_codigo
LEFT OUTER JOIN teste.dbo.CLIENTES1 ON Clientes.cli_codigo = Clientes1.cli1_codigo
WHERE
cli_codigo between 1241 and 1241
) as xx

where Row = 1

4

That is just as good.

If you had this query already, I am guessing that you posted the question to see if there is a more efficient method? The method I posted and what you were using would perform about the same, so there would be no advantage to using what I posted.