CODEWITHSUNDEEP
sql-serverrows
user127815
user127815

Reputation: 141

Getting 4th row of query result

I am using Microsoft SQL Server 2012 and I have this query (below). It returns everything correctly but I cannot figure out how to get only the 4th row to return.

I know how to do it with a simple query but with a query this complex I don't know how to only return the 4th row. Since I am using MS SQL Server, I cannot use Limit/Offset. How and where should I place code to only print the 4th row? I'm stumped. Thanks.

SELECT T.[First Name],
T.[Last Name],
T.[Middle Name],
T.[Country Name],
T.[Designation Name],
Salary.Emp_Salary AS 'Current Salary'
FROM
(
SELECT
    Emp_First_Name AS "First Name",
    Emp_Last_Name AS "Last Name",
    Emp_Middle_Name AS "Middle Name",
    Country_Name AS "Country Name",
    Desig_Name as "Designation Name",
    MAX(Emp_Salary_Change_Year) AS "Time"
    FROM Employee_Details

   INNER JOIN Country ON Employee_Details.Emp_Country_Id = Country.Country_Id
   INNER JOIN State ON Employee_Details.Emp_State_Id = State.State_Id
   INNER JOIN Designation ON Employee_Details.Desig_Id = Designation.Desig_Id
   INNER JOIN Salary ON Employee_Details.Emp_Id = Salary.Emp_Id

   GROUP BY Emp_First_Name, Emp_Last_Name,
   Emp_Middle_Name, Country_Name, Desig_Name) AS T
   INNER JOIN Salary ON T.Time = Salary.Emp_Salary_Change_Year
   ORDER BY [Current Salary];

Upvotes: 2

Views: 548

Answers (3)

Ahmed Saeed
Ahmed Saeed

Reputation: 851

You can also, use the OFFSET/FETCH keyword.

SELECT  * 
  FROM  yourTable
 ORDER  by yourSortColumn
OFFSET  3 ROWS  -- Skip Three rows.
 FETCH  FIRST 1 ROW ONLY;

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133370

You can use TOP this way (first you select only 4 rown and the ordering desc select 1 row)

 select TOP 1 from (
    SELECT  TOP 4 T.[First Name],
    T.[Last Name],
    T.[Middle Name],
    T.[Country Name],
    T.[Designation Name],
    Salary.Emp_Salary AS 'Current Salary'
    FROM
    (
    SELECT
     ........ 
      ) AS T
   INNER JOIN Salary ON T.Time = Salary.Emp_Salary_Change_Year
   ORDER BY [Current Salary]) 
    ORDER BY [Current Salary] DESC

Upvotes: 1

Michael Dolence
Michael Dolence

Reputation: 61

Use a CTE with ROW_NUMBER()

;WITH Cte AS
(
    SELECT Col1, Col2, ...,
         ROW_NUMBER() OVER (ORDER BY ...) AS RowNum
    FROM Table
)
SELECT *
FROM Cte
WHERE RowNum = 4

Upvotes: 6

Related Questions