SQL code in order to select second value after first one-CodePudding

Help needed. Could someone help to generate code which would take only second value of IncurredAmount after first one from the same policid.

SELECT claims.claimid, claims.policyid, claims.IncurredAmount
FROM claims
GROUP BY claims.claimid, claims.policyid, claims.IncurredAmount
HAVING (((claims.policyid)=62));

That's what I have. I tried to take one policyid (62) in order to have less entries. But there I stuck. have no clue what clause can be used in order to take only second entries for all entries.

CodePudding user response：

Try this, though whether it will work depends on the version of your database:

SELECT claimid, policyid, IncurredAmount 
FROM (
    SELECT *, 
        row_number() over (partition by policyid order by claimid) rn
    FROM [MyTable]
) t
WHERE t.rn = 2

CodePudding user response：

A solution exists for the old MySql versions (pre 8.0)

select *
from claims t
where exists (
   select 1
   from claims t2
   where t2.policyid = t.policyid
     and t2.claimid <= t.claimid
   having count(distinct t2.claimid) = 2
)
order by policyid, claimid

db<>fiddle here

Although it's more equivalent to a DENSE_RANK.
I.e. if there's more with the 2nd lowest claimid then it'll get more than 1.