How to get max by colum1 1 then by colum 2 in sql? and is there aggregate function like first or last?

I tried 2 ways:

1-it's get me max date but the max price is not associated with max date:

```
SELECT doc_kits_t.DateOper, kits_t.DocId, kits_t.GoodId,MAX(price_list_t._Date), MAX (price_list_t.Price)
FROM [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
AND kits_t.GoodId = price_list_t.AcGoodsId
GROUP BY kits_t.DocId,kits_t.GoodId,doc_kits_t.DateOper
```

2- I have not found aggregate function to get 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)
FROM [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
AND kits_t.GoodId = price_list_t.AcGoodsId
GROUP BY kits_t.DocId,kits_t.GoodId,doc_kits_t.DateOper
ORDER BY 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 the result i should get table: AS

```
(DateOpr , DocId, Number, TotalPrice )
```

the TotalPrice is the sum of prices of goods that have same DocId where link GoodId in AcKits with GoodId in PriceList It must fulfill the conditions `(DocAcKits.DateOper <= PriceList._Date`

and date is max and if there more then one max date we will get max price)

Thanks for help, the final solution is:

```
;WITH CTE AS
(
SELECT ROW_NUMBER()OVER(PARTITION BY kits_t.GoodId,doc_kits_t.DateOper ORDER BY price_list_t._Date DESC, price_list_t.Price DESC) rn,
doc_kits_t.DateOper as DateOper, kits_t.DocId as DocId,
ISNULL (price_list_t.Price,0) as Price,
kits_t.Count as Count,
doc_kits_t.Number as Number
FROM [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
AND kits_t.GoodId = price_list_t.AcGoodsId
WHERE doc_kits_t.DateOper BETWEEN @StartDate AND @EndDate
)
INSERT @ret
SELECT DateOper, Number, SUM(Price * Count)as TotalPrice FROM CTE t1
WHERE rn = 1
GROUP BY DocId,DateOper,Number
```