sql server - SQL max by 2 column, or first and last aggregate function -

how max colum1 1 colum 2 in sql? , there aggregate function first or last?

i tried 2 ways:

1-it's me max date max price not associated max date:

select doc_kits_t.dateoper, kits_t.docid, kits_t.goodid,max(price_list_t._date), max (price_list_t.price) [ackits] kits_t inner join [docackits] doc_kits_t on  kits_t.docid = doc_kits_t.id left outer join [pricelist] price_list_t on price_list_t._date <= doc_kits_t.dateoper  , kits_t.goodid = price_list_t.acgoodsid  group kits_t.docid,kits_t.goodid,doc_kits_t.dateoper 

2- have not found aggregate function first:

select doc_kits_t.dateoper, kits_t.docid, kits_t.goodid, /*top or first*/(price_list_t._date),/*top or first*/ (price_list_t.price) [ackits] kits_t inner join [docackits] doc_kits_t on  kits_t.docid = doc_kits_t.id left outer join [pricelist] price_list_t on price_list_t._date <= doc_kits_t.dateoper  , kits_t.goodid = price_list_t.acgoodsid  group kits_t.docid,kits_t.goodid,doc_kits_t.dateoper  order price_list_t._date, price_list_t.price 

the full explanation:

i have 3 table:

table 1:     pricelist (id, acproviderid, acgoodsid, price, _date) table 2:     docackits (id, number, dateoper) table 3:     ackits (id, docid, goodid, count)  

at result should table:

(dateopr , docid, number, totalprice ) 

the totalprice sum of prices of goods have same docid link goodid in ackits goodid in pricelist must fulfill conditions (docackits.dateoper <= pricelist._date , date max , if there more 1 max date max price)

thanks help, final solution is:

;with cte  (   select row_number()over(partition kits_t.goodid,doc_kits_t.dateoper order price_list_t._date desc, price_list_t.price desc) rn,   doc_kits_t.dateoper dateoper, kits_t.docid docid,    isnull (price_list_t.price,0) price,   kits_t.count count,   doc_kits_t.number number   [ackits] kits_t   inner join [docackits] doc_kits_t   on  kits_t.docid = doc_kits_t.id   left outer join [pricelist] price_list_t   on price_list_t._date <= doc_kits_t.dateoper    , kits_t.goodid = price_list_t.acgoodsid    doc_kits_t.dateoper between @startdate , @enddate   )   insert @ret   select dateoper, number, sum(price * count)as totalprice  cte t1   rn = 1   group docid,dateoper,number 

you can use row_number() partition by achieve this

;with cte  ( select row_number()over(partition kits_t.docid,kits_t.goodid,doc_kits_t.dateoper order price_list_t._date desc) rn, doc_kits_t.dateoper, kits_t.docid, kits_t.goodid [ackits] kits_t inner join [docackits] doc_kits_t on  kits_t.docid = doc_kits_t.id left outer join [pricelist] price_list_t on price_list_t._date <= doc_kits_t.dateoper  , kits_t.goodid = price_list_t.acgoodsid ) select * cte rn = 1 

note:

if need max record price_list_t._date use order price_list_t._date desc

if need min record price_list_t._date use order price_list_t._date asc