How to

Below is the simple query to detect how many time record is entered in database within one year. Sequence number is generated for the records for the same year from 1,2... and for next year again from 1,2.... and soon. /* Creating table for entries */ CREATE TABLE #entries ( RecordId INT IDENTITY ( 1 , 1 ), FinancialYear INT ) INSERT INTO #entries ( FinancialYear ) VALUES ( 2011 ),( 2011 ),( 2011 ),( 2012 ), ( 2012 ), ( 2012 ), ( 2012 ),( 2012 ), ( 2013 ), ( 2013 ), ( 2013 ) /*Get all data of entry table to temp table with two aditional columns*/ SELECT *, CAST ( 0 AS INT ) MatchYear , CAST ( 0 AS INT ) EntryId INTO #temp FROM #entries DECLARE @id INT = 1 UPDATE #temp SET #temp . MatchYear = a . FinancialYear FROM #entries a WHERE #temp . RecordId = a . RecordId + 1 UPDATE #temp SET EntryId = @id , @id = @id + CASE WHEN FinancialYear = MatchYear THEN 1 ELSE -( @id - 1 ) END SELECT RecordId , Fi

Here below is the simplest logic to find nth MAX salary from Salary Table   DECLARE @Salary TABLE ( EmployeeId INT IDENTITY ( 1 , 1 ), Salary INT ) /* Temporary Salary Table using Table variable*/ DECLARE @n INT = 3 /* nth MAX Salary, here 3 means 3rd MAX salary */ INSERT INTO @Salary ( Salary ) VALUES ( 1000 ), ( 2000 ), ( 3000 ), ( 4000 ), ( 2000 ), ( 1000 ), ( 3000 ) Method 1:- SELECT Salary FROM   (            SELECT ROW_NUMBER () OVER ( ORDER BY ( Salary ) DESC ) Sno ,                   Salary            FROM    @Salary            GROUP BY                   Salary        ) a WHERE   a . Sno = @n Method 2:- SELECT TOP 1 Salary FROM ( SELECT DISTINCT TOP ( @n ) Salary FROM @Salary ORDER BY Salary DESC ) a ORDER BY Salary ASC