Trying to wrap my mind around a terrible SQL query

Question

Can someone please help me understand what exactly this query is doing?

SELECT pp.Sedol ,MAX(MAX(Id)) OVER ( PARTITION BY pp.Sedol ,MAX(pp.ValueDate) ) PriceId FROM Prices pp GROUP BY pp.Sedol 

Answer 1

This is equivalent to:

 with x as ( select Sedol, max(id) max_id, Max(ValueDate) max_valuedate from Prices group by Sedol ) select Sedol, max(max_id) over (partition by Sedol, max_valuedate) PriceId from x; 

Although, as Lamak says, I see no way that this is not just equivalent

 SELECT Sedol, MAX(Id) PriceId FROM Prices GROUP BY Sedol 

SQL Fiddle

Answer 2

I think Lamak already explained about sql, I am posting an example for understanding. You can see that sqls give the same results. Copy the paste below the code on the sql server and try:

 declare @Prices table (Id int, ValueDate datetime, Sedol int) Insert into @Prices values (1,'2014-09-06' ,200), (2,'2014-09-07' , 100), (3,'2014-09-08' , 100), (4,'2014-09-09' , 100), (5,'2014-09-10' , 300), (6,'2014-09-11' , 300), (7,'2014-09-12' , 100), (8,'2014-09-13' , 200), (9,'2014-09-14' , 200), (10,'2014-09-15' , 200) Select * from @Prices -- Your SQL SELECT pp.Sedol ,MAX(MAX(Id)) OVER ( PARTITION BY pp.Sedol ,MAX(pp.ValueDate) ) PriceId FROM @Prices pp GROUP BY pp.Sedol -- Simple SQL mentioned by Lamak SELECT Sedol, MAX(Id) PriceId FROM @Prices GROUP BY Sedol 

Result: