You can replace the “Table” table with your table name and “N” with the level of salary to be determined.
Here is query to find Nth Highest Record from Database Table.
Method 1: Using sub query
SELECT *
FROM Table t1
WHERE (N-1) = (SELECT COUNT(DISTINCT(t2.salary))
FROM Table t2
WHERE t2.salary > t1.salary)
Method 2: Using Top
SELECT TOP 1 Salary
FROM (
SELECT DISTINCT TOP N Salary
FROM Table1
ORDER BY Salary DESC
) AS Emp
ORDER BY Salary
Few days back, my friends had asked a question “I want to get only integer part in string. for example, string contain ‘asb645pqr231means’ i want only integer part like 64523. how can i get?“. I have mentioned two method to do it.
Method 1:
SET NOCOUNT ON
DECLARE @loop INT
DECLARE @str VARCHAR (8000)
SELECT @str = ‘asb645pqr231’
SET @loop = 0
WHILE @loop < 26
BEGIN
SET @str = REPLACE (@str, CHAR (65 + @loop), ”)
SET @loop = @loop + 1
END
SELECT @str
Method 2:
SET NOCOUNT ON
DECLARE @KeepValues VARCHAR (50)
DECLARE @Str VARCHAR (500)
SET @Str= ‘asb645pqr231’
SET @KeepValues = ‘%[^a-z]%’ — if you want only characters in the output
SET @KeepValues = ‘%[^0-9]%’ — If you want only integers in the output
WHILE PATINDEX (@KeepValues, @Str) > 0
SET @Str = STUFF (@Str, PATINDEX(@KeepValues, @Str), 1, ”)
select @Str
There may be more ways do it.
In MS SQL Server, we can use a CASE statement to implement IF…THEN. Here is the example of the CASE statement.
If your logic is as follows:
IF 1 > -1
THEN
‘TRUE’
ELSE
‘FALSE’
You can use CASE statement as follows:
— SQL Server 2008 and earlier version solution
SELECT CASE WHEN -1 < 1 THEN ‘TRUE’ ELSE ‘FALSE’ END AS Result
GO
A New function “IIF” has introduced in MS SQL 2012. This function is shorthand way for writing CASE statement. These functions take three arguments. If the first argument is true, it will return the second argument as result or it will return the third argument as result.
— SQL Server 2012 solution
Example 1: IIF Usage
SELECT IIF ( -1 < 1, ‘TRUE’, ‘FALSE’ ) AS Result;
GO
Example 3: IIF with NULL
DECLARE @Variable INT=NULL
SELECT IIF ( @Variable IS NULL, ‘Value is Null’, ‘Value is NOT NULL’ ) AS Result;
GO
Example 2: Nested IIF
SELECT IIF ( 1 > -1, IIF ( 1 = 1, ‘Inner True’, ‘Inner False’ ), ‘FALSE’ ) AS Result;
In above example, we can see how IIF is used instead of CASE statement. IIF can be used the same way as CASE statement in SELECT statement.
I have tried to explain the usage of simple WHILE loop in first example. BREAK keyword will exit the stop the while loop and control is moved to next statement after the while loop. CONTINUE keyword skips all the statement after its execution and control is sent to first statement of while loop. Run following examples in Query Editor and see the result. This is very easy to understand example.
1. Example of WHILE Loop
DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
END
GO
ResultSet:
1
2
3
4
5
2. Example of WHILE Loop with BREAK keyword
DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
IF @intFlag = 4
BREAK;
END
GO
ResultSet:
1
2
3
3. Example of WHILE Loop with CONTINUE and BREAK keywords
DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
CONTINUE;
IF @intFlag = 4 — This will never executed
BREAK;
END
GO
ResultSet:
1
2
3
4
5
