Search code examples
sqlmaxpartition-by

SQL max() over(partition) returning multiple results

I have a simple query that I'm using to try and understand the SQL max() over() functionality as follows:

select *, max(mt.version) over (partition by mt.version)
from myTable mt
where mt.id = 'some uuid';

Edit: This concept is new to me so I'm not sure what it is supposed to do. I don't have a specific data set, I'm just simply trying to understand what the code is doing by coming up with my own example.

The thing I don't understand is that I am getting multiple rows with my data set. I thought the max() over(partition by x) functionality was supposed to return a single result based off of the max function. Will someone please explain why I would get more than 1 result. I tried this on a different table and it works fine but on another table it doesn't work.

Solution

  • I was taking the max of mt.version and partitioning it by mt.version so I wasn't getting any new data. A correct version of what I previously wrote could be:

    select *, max(mt.version) over (partition by mt.partitiongroup)
from mytable mt
where mt.id = 'some uuid';

    I wasn't realizing while trying out this new concept that the partition group had to be different than the max aggregation.