Getting the second row in a Table in SQL-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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;

CodePudding user response：

if you just want to fetch the penultimate row, there should not be a need to keep rownum as a separate column. In the below query, the count and the current row number is compared to ascertain if that is the penultimate one.

select * 
from(
select *
,case when row_number()over(partition by invoiceDate,productContractId
order by invoiceDate) = (count(*)over(partition by invoiceDate,productContractId
order by invoiceDate)) - 1
then 'fetch' end as [toBeFetched]
from
[yourtable]
)t
where [toBeFetched] = 'fetch'