Get number of rows from query

Question

I'm trying to make a sheet where I always have to get the last score of a player.

But I can't use the offset to get the last row because I don't know the number of rows from the query result. I don't know if there is a way to use CountA, I couldn't use it!

This is my sheet: https://docs.google.com/spreadsheets/d/1SZAbTUiGsUhTbKVPF9lGZzJhMPqCCMp1VQ0F-Q4zXcw/edit?usp=sharing

I'm trying this query:

=QUERY($A$2:$D2; "select D where B='" & $B2 &"' and C='"& $C2 &"' ")

Answer 1

use:

=ARRAYFORMULA(QUERY({$A$2:$D6\ ROW($A$2:$A6)}; 
 "select Col4 
  where Col2='" & $B6 &"' 
    and Col3='"& $C6 &"' 
  order by Col5 desc 
  limit 1"; 0))

---

update:

=ARRAYFORMULA(IFNA(VLOOKUP(
 B2:B&C2:C&COUNTIFS(B2:B&C2:C; B2:B&C2:C; ROW(B2:B); "<="&ROW(C2:C)); {
 B2:B&C2:C&COUNTIFS(B2:B&C2:C; B2:B&C2:C; ROW(B2:B); "<="&ROW(C2:C))+1\ 
 D2:D}; 2; 0); D2:D))