Hi @Sudip Bhatt ,
Actually Erland has provided all the details.
Only adding some notes here.
this line not clear WHERE ROWASC IN ( ROWDESC, ROWDESC - 1, ROWDESC + 1 )
If it contains an odd number of values, it will refer 'where ROWASC =ROWDESC'.
If it contains an even number of values, it will refer 'where ROWASC =ROWDESC+1 or ROWASC =ROWDESC-1 '.
So this where condition will include both situations.
PERCENTILE_CONT(0.5) why .5 is sending ? why not different value ?
Syntax:
PERCENTILE_CONT ( numeric_literal )
WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] )
OVER ( [ <partition_by_clause> ] )
You could send any decimal between 0.0 and 1.0.
In your situation, PERCENTILE_COUNT(0.5) is similar to finding median.
You could use below query to find out the differences.
CREATE TABLE MEDIAN
(
Number int not null
);
insert into MEDIAN select 2;
insert into MEDIAN select 4;
insert into MEDIAN select 9;
insert into MEDIAN select 15;
insert into MEDIAN select 22;
insert into MEDIAN select 26;
insert into MEDIAN select 37;
insert into MEDIAN select 49;
insert into MEDIAN select 66;
SELECT Number,
PERCENTILE_CONT(0) WITHIN GROUP (ORDER BY Number) OVER () AS MINCOUNT,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY Number) OVER () AS QUARTTERCOUNT,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Number) OVER () AS MEDIANCONT ,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY Number) OVER () AS THREEQUARTTERCOUNT ,
PERCENTILE_CONT(1) WITHIN GROUP (ORDER BY Number) OVER () AS MAXCONT
FROM MEDIAN
what WITHIN GROUP() does ?
This specifies a list of numeric values to sort and compute the percentile over. Only one order_by_expression is allowed.
why Over() is blank ?
It divides the result set produced by the FROM clause into partitions to which the percentile function is applied.
In your case, there is only one column number, then you could leave it as blank.
If you have another column, for example Groupid, then you need to put 'PARTITION BY Groupid' inside OVER().
Please refer below example:
DROP TABLE IF EXISTS MEDIAN
CREATE TABLE MEDIAN
(
Groupid int not null,
Number int not null
);
insert into MEDIAN select 1,2;
insert into MEDIAN select 1,4;
insert into MEDIAN select 1,9;
insert into MEDIAN select 1,15;
insert into MEDIAN select 2,22;
insert into MEDIAN select 2,26;
insert into MEDIAN select 2,37;
insert into MEDIAN select 2,49;
insert into MEDIAN select 2,66;
SELECT Number,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Number) OVER (PARTITION BY Groupid) AS MEDIANCONT
FROM MEDIAN
Best regards
Melissa
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table