select max(invoiceno), salesperson, qty
from salesmaster
where
invoicedate between '2016-01-01' and '2016-04-01' and delflag = 'N'
group by salesperson
Try this:
with cte
as (select invoiceno
,salesperson
,qty
,row_number() over(partition by salesperson
order by invoiceno desc
)
as rn
from salesmaster
where invoicedate>=cast('2016-01-01' as date)
and invoicedate<cast('2016-04-02' as date)
and delflag='N'
)
select invoiceno
,salesperson
,qty
from cte
where rn=1
;
Or this (I'm guessing invoiceno can hold only one salesperson):
select b.invoiceno
,b.salesperson
,b.qty
from (select max(invoiceno) as invoiceno
from salesmaster
where invoicedate>=cast('2016-01-01' as date)
and invoicedate<cast('2016-04-02' as date)
and delflag='N'
group by salesperson
) as a
inner join salesmaster as b
on b.invoiceno=a.invoiceno
;