Order of processing of windows function

• protocoder

  Old Hand

  Points: 374

  More actions

  March 26, 2022 at 1:29 pm

  Go to Answer

  #4006354

  Hi Experts,

  Please can you let me know the order of processing of the window functions,

  1. The second in order DENSE_RANK is executed first before the first one, is this right ?
  2. 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

• Phil Parkin

  SSC Guru

  Points: 246986

  More actions

  March 26, 2022 at 1:44 pm

  Answer

  #4006357

  1. Whatever the optimiser chooses - the calculation of one is not dependent on the other
  2. 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.

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• Jeff Moden

  SSC Guru

  Points: 1004409

  More actions

  March 26, 2022 at 9:57 pm

  #4006467

  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)