asked
Ordering Data in Specific manner
Hi Everyone, I have a requirement where from a View need to get result in a specific sorting order. Here is sample table and expected output- Figure 1 - is the output of Select * from dbo.view_name; Figure 2 - is how data has to sorted. ![alt text][1] The data has to arrange in Descending order based on the last day BASE_PERCENTAGE. So Last day is 04-Feb-2017 and the highest to lowest order is Jessie, Ricky, Jammie and Jasmine. The final output should arrange data based on BASE_PERCENTAGE, CURR_DATE and Name. Could someone please help to how to get the same result using sql. How sql can be written to that it gives the same result. Please let me know if you need more explanation. Attached Table_View_sql.sql is having table create, Sample Data Insert and View creation. View will give the data with BASE_Percentage. [File Link][2] Thanks, Anupam [1]: /storage/temp/3969-table-raw-sort.jpg [2]: /storage/temp/3968-table-view-sql.sql
Try this: SELECT * FROM dbo.v_DailyDetails AS vdd ORDER BY FIRST_VALUE(vdd.base_percentage) OVER (PARTITION BY vdd.name ORDER BY vdd.curr_date DESC) DESC, curr_date DESC, name; The trick is in the first line of the `ORDER BY`. The `FIRST_VALUE` partition function returns the, erm, first value of the subset of the results based on partition (we're grouping / partitioning on vdd.name), ordered by the current date descending. ie the most recent value. * [FIRST_VALUE - MSDN documentation][1] * [Other analytic functions in T-SQL][2] * [OVER clause - MSDN][3] [1]:
https://msdn.microsoft.com/en-us/library/hh213018.aspx [2]:
https://msdn.microsoft.com/en-us/library/hh213234.aspx [3]:
https://msdn.microsoft.com/en-us/library/ms189461.aspx
Waw ThomasRushton, I just ran your sql and it seems it is giving result as expected. It seems FIRST_VALUE is newly introduced to the sql server. By any chance do you have list of newly introduced function. Thank you for the help!! Otherwise i was thinking of creating another view on top it with rank.
There are pages such as this that show new functionality for SQL Server 2014:
https://msdn.microsoft.com/library/bb500435(v=sql.120).aspx A little bit of hunting around finds this for the new Analytic functions (of which `FIRST_VALUE` is one) in SQL Server 2012:
https://msdn.microsoft.com/en-us/library/09f0096e-ab95-4be0-8c01-f98753255747(v=sql.110)#Analytic Functions
18 People are following this question.
