string reqSql = @"
select * into #pvtmp from (
select id,journal,piece,id_cli,date,reference,type,ROW_NUMBER() OVER (PARTITION BY p2.id_cli ORDER BY p2.date desc,p2.type desc) as rn from pieds_v p2
) t
where rn=1
select c.C_ID,c.C_NOM,c.C_REGIME,p.DATE,p.TYPE,p.REFERENCE from CLI c
left join #pvtmp p on (c.c_id=p.id_cli)
drop table #pvtmp";
List<CLI_LAST_SALE> l = Api.Zselect<CLI_LAST_SALE>(MercatorUi.Globals.RepData, reqSql);