Select second largest value 1

[sila]

I have used the sql below to extract a max value but I now need to select the second largest value within the id. Does anyone know how to do this?

SELECT MAX(VotesReceived) AS Expr1
FROM dbo.Candidate a
WHERE (DivElecID = @DivElecID)

Thanks

 

[PWise]

SELECT MAX(VotesReceived) AS Expr1
FROM dbo.Candidate a
inner join (SELECT MAX(VotesReceived) AS Expr1
FROM dbo.Candidate WHERE (DivElecID = @DivElecID)
)realmax on a.VotesReceived<realmax.Expr1
WHERE (DivElecID = @DivElecID)

 

[dbomrrsm]

SELECT     MAX(VotesReceived) AS Expr1
FROM         dbo.Candidate a
WHERE     (DivElecID = @DivElecID)
and VotesReceived < (SELECT MAX(VotesReceived)
 FROM dbo.Candidate where DivElecID = @DivElecID))

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[vongrunt]

Another way:

select max(VotesReceived) As blah
from Candidate
where DivElecID = @DivElecID 
	and VotesReceived < 
	(	select max(VotesReceived) 
		from Candidate 
		where DivElecID = @DivElecID 
	)

Yet another way:

select min(blah) as blah
from
(	select top 2 VotesReceived As blah	
	from Candidate
	where DivElecID = @DivElecID 
	order by VotesReceived desc
) X

Yetyet another way (for single scalar value as in your example):

declare @blah int
select top 2 @blah=VotesReceived
	from Candidate
	where DivElecID = @DivElecID 
	order by VotesReceived desc

And last but not least: get TOP 2, skip 1st record client-side (ADO/JDBC/whatever)

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[JamesLean]

SELECT MAX(votesreceived)
FROM candidate
WHERE divelecid = @divelecid
  AND votesreceived < (
    SELECT MAX(votesreceived)
    FROM candidate
    WHERE divelecid = @divelecid
  )

--James

 

[simonchristieis]

hey - just up my street - a hack !!!

SELECT    MAX(VotesReceived) AS Expr1
FROM         dbo.Candidate a
WHERE     (DivElecID = @DivElecID)
and VotesReceived not in (
SELECT    MAX(VotesReceived) AS Expr1
FROM         dbo.Candidate a
WHERE     (DivElecID = @DivElecID)
)

 

[chamilz]

can we use this?!!

select top 1 VotesReceived Expr1
from dbo.Candidate
where VotesReceived <> (select max(VotesReceived) from dbo.Candidate )
and DivElecID = @DivElecID
order by VotesReceived desc


/chamil

 

[vongrunt]

No, unless you correlate subquery (ATM it returns global max).

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[chamilz]

got your point vongrunt...is this right ?


select top 1 VotesReceived Expr1
from dbo.Candidate
where VotesReceived <> (select max(VotesReceived) from dbo.Candidate where DivElecID = @DivElecID)
and DivElecID = @DivElecID
order by VotesReceived desc

 

[vongrunt]

Yes.

One thing to consider: what if two VotesReceived are tied for 1st place? Most of queries posted in this thread would return first greatest value after that (2nd largest distinct value). My 1st query also works that way, but 2nd and 3rd don't. I intentionally did that to see reactions - and eventually open discussion - without success .

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[JamesLean]

I now need to select the second largest value within the id

I personally took this to mean the second largest distinct value. But I guess only the original poster knows what he would actually want...

--James