Enable SQL query in Lookup activity GUI

Is there a way, after pointing a pipeline Lookup activity to a lakehouse, to actually query said LH to get back a singleton? For ex: let's say you have a column named INDEX. You want to lookup the maximum INDEX value currently stored in said column. Can you specify the following SQL query?

SELECT MAX([INDEX]) 

FROM LH.TABLE

I don't see anything in the Lookup GUI that allows to do this. Is there a way? Lest we forget: lakehouses have SQL endpoints, therefore why is SQL querying not available from the Fabric Lookup activity?

As things stand, Lookup is useless if you can only retrieve the first row. What does 'first row' even mean? Is there some guaranteed default sort order that is not specified anywhere on scree so it's anybody's guess?