Provide PREORDER BY clause for RANK() and DENSE_RANK() - by ErikEckhardt

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 781881 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 3/21/2013 4:36:40 PM
Access Restriction Public


In relational problems that require grouping together specific rows to perform aggregates, it is sometimes the nature of the data that groups with similar data can only be distinguished by their ordering among all the rows in the set. For example, consider a sensor sampling at various times whether a device is running, outputting to this simple table: CREATE TABLE dbo.DeviceState (SampleTime datetime NOT NULL, Running bit NOT NULL) . We wish to collapse all rows where the state did not change into single rows in the form `MinSampleTime, MaxSampleTime, Running` with the state for each row in ascending time order alternating between Running and Not Running. Coming up with a query to perform this task is difficult. The RANK() function immediately comes to mind as a possible way to distinguish the Running groups from the Not Running groups, but has a fatal flaw: The value changes as soon as the time changes. What we need is a way to cause RANK() to order the rows by the SampleTime before the Status time, but then not use the changes in the SampleTime column to calculate the actual series values--depending only on the Running column to trigger an increment.
Sign in to post a comment.
Posted by Umachandar [MSFT] on 4/12/2013 at 2:07 PM
Tha ranking functions in SQL Server follow the ANSI SQL specifications and we have no intention to extend it with SQL Server specific syntax.

Umachandar, SQL Programmability Team