CODEWITHSUNDEEP
sqlsql-serversql-server-2008greatest-n-per-group
goofyui
goofyui

Reputation: 3492

In SQL Server, how to pick top 4th row?

What is the SQL query to pick top 4th row for every Id# ?

It is not Top 4 row of records. I am trying to pick only 4th row of record / column for every id.

Upvotes: 3

Views: 377

Answers (2)

AT-2017
AT-2017

Reputation: 3149

You can use the following query and pass the required ID of the table to get the specific 4th row:

SELECT * 
FROM 
    (SELECT 
         m.CityId, m.Country, m.Location, m.NoofDweller, 
         ROW_NUMBER() OVER (ORDER BY Country ASC) AS RowNumber 
     FROM 
         Cities m 
     WHERE 
         m.Country = 3 -- Just pass the required ID of the table 
    ) AS Details
WHERE 
    RowNumber = 4 -- Gets details of the 4th row of the given ID

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28900

Use the row_number function:

With cte as
(
    select 
        *,
        row_number() over (partition by id order by id) as rownum
    from 
        table 
)
select * 
from cte 
where rownum = 4

Change order by in partition according to your definition of top

Upvotes: 7

Related Questions