declare @t table
(
Num int
)
insert into @t values (10),(20),(30),(50)
select Num,Percent_Rank() over (order by Num) as PrcntRank
from @t
/*Output
Num PrcntRank
10 0
20 0.333333333333333
30 0.666666666666667
50 1
*/
declare @t table
(
GrpId int,
Num int
)
insert into @t values (1,10),(1,20),(1,30),(1,50)
select *,
PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY Num) OVER (PARTITION BY GrpID) as [InterPolatedAt0.4],
PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY Num) OVER (PARTITION BY GrpID) as [InterPolatedAt0.8]
from @t
/*
GrpId Num InterPolatedAt0.4 InterPolatedAt0.8
1 10 22 28
1 20 22 28
1 30 22 28
1 50 22 38
*/
declare @t table
(
GrpId int,
Num int
)
insert into @t values (1,10),(1,20),(1,30),(1,50)
select *,
PERCENTILE_DISC(0.4) WITHIN GROUP (ORDER BY Num) OVER (PARTITION BY GrpID) as [InterPolatedAt0.4],
PERCENTILE_DISC(0.8) WITHIN GROUP (ORDER BY Num) OVER (PARTITION BY GrpID) as [InterPolatedAt0.8]
from @t
/*
GrpId Num InterPolatedAt0.4 InterPolatedAt0.8
1 10 20 50
1 20 20 50
1 30 20 50
1 50 20 50
*/
DECLARE @Users TABLE(
UserID INT IDENTITY,
UserName VARCHAR(20)
)
INSERT INTO @Users (UserName)
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D'
SELECT
*,
LAG(UserName) OVER (ORDER BY UserName) AS Prev,
LEAD(UserName) OVER (ORDER BY UserName) AS Next
FROM @Users
ORDER BY UserName
/*
UserID UserName Prev Next
----------- -------------------- -------------------- ----------------------
1 A NULL B
2 B A C
3 C B D
4 D C NULL
*/
declare @Salaries table
(
DeptId int,
Sal int
)
insert into @Salaries values (1,23),(1,25),(1,21),(2,35),(2,15)
select DeptID,Sal,last_value(Sal) over (partition by DeptID order by Sal) as LastSalary.
First_value(Sal) over (partition by DeptID order by Sal) as FirstSalary.
from @Salaries
DeptID Sal LastSalary FirstSalary
1 21 25 21
1 23 25 21
1 25 25 21
2 15 35 15
2 35 35 15
SELECT CONCAT(@lastname, ' ', @middlename, ' ', @firstname)
SELECT EOMONTH('12/06/2011')
OutPut:
2011-12-31 00:00:00.0000000
SELECT IFF (@Number1 > @Number2, @true_value, @falsevalue)
SELECT CHOOSE (@Index, @FirstValue, @SecondValue, @ThirdValue)
SELECT PARSE('Monday, 13 December 2010' AS datetime2 USING 'en-US') AS Result
GO
SELECT PARSE('€345,98' AS money USING 'de-DE') AS Result
/*Output
2010-12-13 00:00:00.0000000
*/
SELECT TRY_PARSE('2011-01-01' AS datetime2 USING 'en-US') AS Result
GO
SELECT TRY_PARSE('Test' AS datetime2 USING 'en-US') AS Result
SET DATEFORMAT mdy;
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;
GO
SET DATEFORMAT dmy;
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;
GO
/*
2010-12-31 00:00:00.0000000
NULL
*/
SELECT DATETIMEFROMPARTS(
@Year,
@Month,
@Day,
@Hour,
@Minute,
@Seconds,
@MilliSeconds)
DECLARE @CurrentDate DATETIME, @Format NVarchar(20), @Culture NVarchar(10)
SET @CurrentDate = GETDATE(); -- (12/6/2011)
SET @Format = 'dd-mm-yyyy'
SET @Culture = 'en-US'
SELECT FORMAT(@CurrentDate,@Format, @Culture)
CREATE SEQUENCE CustomSequence AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 10000;
GO
CREATE TABLE TEST1
(
Id int,
Col1 varchar(100)
)
GO
CREATE TABLE TEST2
(
ID int,
Col2 varchar(100)
)
GO
INSERT INTO TEST1 values (NEXT VALUE FOR Custom_Sequence,'a')
INSERT INTO TEST2 values (NEXT VALUE FOR Custom_Sequence,'b')
SELECT * From Test1
SELECT * From Test2
EXECUTE YourProcedure
WITH RESULT SETS
(
(
Col1 INT,
Col2 BIGINT
)
);
Select Name , Address, ROW_NUMBER() OVER (ORDER BY NAME) RN FROM TABLE
WHERE RN Between 2 AND 5
SELECT
*
FROM Table
ORDER BY ID
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)