# Getting the Second Highest Number With Conditions

Hello all.

I am getting stumped trying to find the second highest value using some conditions. When I use the statement below, I still get 330397, which is the highest value in the list.

I am trying to get 329137. Where am I going astray? Thank you.

Select
MAX(dc.DebitCardKey)
From
DebitCard dc
Where
dc.DebitCardKey < (Select MAX(dc.DebitCardKey) From DebitCard dc)
AND dc.MemberNumber = 1XXXXX0
AND dc.CreditCardAccount = 125

``````
Select Top (1)
DebitCardKey
From (
Select Top (2)
dc.DebitCardKey
From
DebitCard dc
Where
dc.MemberNumber = 1XXXXX0
AND dc.CreditCardAccount = 125
Order By
DebitCardKey Desc
) As derived
Order By DebitCardKey
``````

Select
MAX(dc.DebitCardKey)
From
DebitCard dc
Where
dc.DebitCardKey < (Select MAX(dc.DebitCardKey) From DebitCard dc_ WHERE dc_.MemberNumber = 1XXXXX0 AND dc_.CreditCardAccount = 125)
AND dc.MemberNumber = 1XXXXX0
AND dc.CreditCardAccount = 125

hi hope this helps

create sample data script

drop table if exists #DebitCard
create table #DebitCard ( DebitCardKey int )
insert into #DebitCard select 134567
insert into #DebitCard select 134576
insert into #DebitCard select 134569
insert into #DebitCard select 134570

select * from #DebitCard order by DebitCardKey desc

``````select
DebitCardKey
from
(
select
DebitCardKey
,  dense_rank() over(order by  debitcardkey desc) as r
From
#DebitCard
) a
where
r=2;
``````

Another option:

``````SELECT TOP 1 LEAD(dc.DebitCardKey,1) OVER (ORDER BY dc.DebitCardKey DESC)
FROM DebitCard dc
WHERE dc.MemberNumber = 1XXXXX0
AND dc.CreditCardAccount = 125
ORDER BY dc.DebitCardKey DESC
``````

Sweet. Thank you all for responding. I was able to get the desired result from all of your suggestions. Appreciate it.