Order of processing of windows function
-
Hi Experts,
Please can you let me know the order of processing of the window functions,
- The second in order DENSE_RANK is executed first before the first one, is this right ?
- when I add group by to the below commands, there is no change whatsoever. May I know why group by has no impact
Select
t.TrackId, t.Name, t.AlbumId, T.MediaTypeId, t.GenreId, T.Composer, T.Milliseconds, T.UnitPrice
,DENSE_RANK() over( order by T.Milliseconds desc) As TrackDurationRank -- 1
,DENSE_RANK() over (Partition by t.AlbumId order by T.Milliseconds desc) as TrackdurationPerAlbum -- 2
FROM dbo.Track t;
--Order by T.AlbumId asc, T.Milliseconds desc
Go
Select
t.TrackId, t.Name, t.AlbumId, T.MediaTypeId, t.GenreId, T.Composer, T.Milliseconds, T.UnitPrice -- 1
,DENSE_RANK() over (Partition by t.AlbumId order by T.Milliseconds desc) as TrackdurationPerAlbum --2
,DENSE_RANK() over( order by T.Milliseconds desc) As TrackDurationRank
FROM dbo.Track t;
--Order by T.AlbumId asc, T.Milliseconds desc
Go
Thanks a lot for help
-
- Whatever the optimiser chooses - the calculation of one is not dependent on the other
- Unless you also show us the code containing the GROUP BY, this is difficult to answer. But it's probably because your grouping is such that there is no aggregation occurring.
-
It's real simple... Group By does not guarantee the order of the output, period. Only an ORDER BY will make that guarantee.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply