Getting the Second Highest Number With Conditions

Transact-SQL
1

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.

image

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

2 

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
3

To stay close to your answer:

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

5

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;

image

6

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
7

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