2

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.

Sanushi Salgado
  • 1,195
  • 1
  • 11
  • 18
Michael Hogenson
  • 1,292
  • 1
  • 15
  • 31
  • 3
    Well, you're only taking one maximum for each distinct value of `mt.version`... In fact, since you're taking the max of `mt.version` and partitioning it by `mt.version`, I don't think you'll get any new data. What are you **actually** trying to do? –  Jul 29 '13 at 22:34
  • 1
    Why did you use `over` if you don't know what it does? It's optional, you know? It looks like you just want the `max` without `over (partition by)` – Blorgbeard Jul 29 '13 at 22:36
  • I'm playing around with it as suggested in the question as suggested by the text, "understand ... functionality". I saw something similar online and was trying to figure out what it did. Hence using it when I don't know what it does... That's how you learn isn't it? – Michael Hogenson Jul 29 '13 at 22:42

1 Answers1

2

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.

Michael Hogenson
  • 1,292
  • 1
  • 15
  • 31