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

Question

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.

Answer 1

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

Answer 2

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