x

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

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

Thanks, Anupam

more ▼

asked Feb 15 at 07:11 AM in Default

avatar image

mail2anupam.kr
11 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Feb 15 at 10:39 AM

avatar image

ThomasRushton ♦♦
42k 20 51 53

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.

Feb 15 at 11:39 AM mail2anupam.kr

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

Feb 15 at 11:52 AM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x119
x69
x28
x9

asked: Feb 15 at 07:11 AM

Seen: 49 times

Last Updated: Feb 15 at 11:52 AM

Copyright 2017 Redgate Software. Privacy Policy