Getting the second row in a Table in SQL

Question

I have a view. In this view, I got the row_num based on the productcontracted, InvoiceDate.

Can you please let me know how I can get the second row in each group?

Answer 1

As you've already got the row_number() setup how you want it, then all you need to do is filter on that row_number in the where statement. This often means turning your query into a subquery, something like the below.

    select * from 
    (
        <<your main query here>>
    )
    where row_number = 2

Answer 2

Looking at your data it looks like you have already applied row_number() over in your query. To get the second row is therefore: row_number = 2 .
According to the highlighted rows you also want the row before last for each partition. To do this you can reverse the order by and then get the second row in each direction. Your query will be something like the following

with cte as
( InvoiceDate,
  ProductContractId,
  row_number() over (
      partition by ProductContractId
      order by InvoiceDate asc) rn_forwards
  row_number() over (
      partition by ProductContractId
      order by InvoiceDate desc) rn_backwards )
select 
  InvocieDate,
  ProductContractId,
  rn_forwards,
  rn_backwards
from cte
where 
  rn_forwards = 2 
  or rn_backwards = 2;