Max function

Question

I'm new to Databricks SQL. I'm trying to use the Max function but it's not working like it typically does in SQL Server. I would normally do something like this:

select ID
, max(QTY) as QTY

from table

group by ID

but Azure is making me put QTY in the group by and even then it's not working

Answer 1

If you are using PySpark with Databricks, then you will find here examples of how to use not only Max aggregate funcions but other functions to.

Put all values in an array, explode it and then max that. For example:

%sql
WITH cte AS
(
SELECT *, EXPLODE( ARRAY( date1, date2 ) ) xmax 
FROM tmp
)
SELECT MAX( xmax )
FROM cte

Answer 2

Appreciate you. I've never heard of Max Array! I'll definitely look into it! And, I'm not sure what PySpark is. I just know it looks like SQL. lol. I have years of SQL Server experience and the syntax for this code is different. I'm just used to the Max example that I gave above.

So, I don't need to use a Group By?