Find difference between maximum and second maximum

[i***@gmail.com]

I have a table candidate with following columns and data
CandidateId int
CandidateName varchar(100)
NumberofVotes int
CountyId(FK) int

CountyId(int) in table candidate is foreign key from County table. County table has CountyId(int) and CountyName (varchar 100).

I would like to get the candidatename, countyname and numberofvotes by which a candidate is leading in a particular county.

For example in the below example

CandidateId CandidateName NumberofVotes CountyId
1 John 100 1
2 Mary 44 1
3 Tomy 62 1
4 Bill 20 1

In CountyId=1 John is leading by 38 votes from second highest candidate so this query should return me.

John (countyname) 38

[Bob Barrows]

Post by i***@gmail.com
I have a table candidate with following columns and data
CandidateId int
CandidateName varchar(100)
NumberofVotes int
CountyId(FK) int
CountyId(int) in table candidate is foreign key from County table.
County table has CountyId(int) and CountyName (varchar 100).
I would like to get the candidatename, countyname and numberofvotes
by which a candidate is leading in a particular county.
For example in the below example
CandidateId CandidateName NumberofVotes CountyId
1 John 100 1
2 Mary 44 1
3 Tomy 62 1
4 Bill 20 1
In CountyId=1 John is leading by 38 votes from second highest
candidate so this query should return me.
John (countyname) 38

There's lots of ways to skin this cat. Here's one that assumes SQL 2008+
Assuming the candidateID column has nothing to do with the candidate's
position in the race:

;WITH r AS (
SELECT ROW_NUMBER() OVER(ORDER BY NumberofVotes DESC) AS pos,
CandidateID,CandidateName,CountyId,NumberofVotes
FROM Candidate)
, r1 AS (
SELECT CandidateID,CandidateName,CountyId,NumberofVotes
FROM r WHERE pos = 1)
, r2 AS (
SELECT CandidateID,CandidateName,CountyId,NumberofVotes
FROM r WHERE pos = 2)
SELECT.CountyName, r1.CandidateID,r1.CandidateName,r1.NumberofVotes -
COALESCE(r2.NumberofVotes,r1.NumberofVotes) AS VoteLead
FROM
r1
INNER JOIN County AS c on r1.CountyID = c.CountyID
LEFT JOIN r2 ON r1.CountyID = r2.CountyID

If there is a tie for first place, this query will return both candidates in
the lead, with the vote difference from the 3rd place candidate.
The LEFT JOIN and COALESCE handle the case that there are only two
candidates and they are tied for the lead.

[Erland Sommarskog]

Post by Bob Barrows
There's lots of ways to skin this cat. Here's one that assumes SQL 2008+

SQL 2005+

Post by Bob Barrows
Assuming the candidateID column has nothing to do with the candidate's
;WITH r AS (
SELECT ROW_NUMBER() OVER(ORDER BY NumberofVotes DESC) AS pos,

There should probably be PARTITION BY CountyID in the OVER clause.

Here is a single-pass solution that requires SQL 2012:

WITH CTE AS (
SELCET CandidateName,
NumberOfVotes - LAG(NumberOfVotes, 1, 0)
OVER(PARTITION BY CountyID
ORDER BY NumberVotes DESC) AS Lead,
rowno = row_number() OVER(PARTITION BY CountyID
ORDER BY NumberVotes DESC)
FROM tbl
)
SELECT CountyId, CandidateName, Lead
FROM CTE
WHERE rowno = 1

LAG returns a previous row in the result set as defined by the OVER
clause. If you just say LAG(col) that is the directly previous row.
The second argument specifies how many rows to go back, 1 in this case
(and, yes that is the default). The final argument specifies which
value to return when there is no previous row. Default: NULL.

LAG has twin function that operates in the opposite direction. The
name of that function is LEAD.

[Bob Barrows]

Post by Erland Sommarskog

Post by Bob Barrows
There's lots of ways to skin this cat. Here's one that assumes SQL 2008+

SQL 2005+

Really? I'll take your word for it.

Post by Erland Sommarskog

Post by Bob Barrows
Assuming the candidateID column has nothing to do with the
;WITH r AS (
SELECT ROW_NUMBER() OVER(ORDER BY NumberofVotes DESC) AS pos,

There should probably be PARTITION BY CountyID in the OVER clause.

No "probably" about it. That was definitely an oversight that would have
been caught if I had tested my solution.

Post by Erland Sommarskog
WITH CTE AS (
SELCET CandidateName,
NumberOfVotes - LAG(NumberOfVotes, 1, 0)
OVER(PARTITION BY CountyID
ORDER BY NumberVotes DESC) AS Lead,
rowno = row_number() OVER(PARTITION BY CountyID
ORDER BY NumberVotes DESC)
FROM tbl
)
SELECT CountyId, CandidateName, Lead
FROM CTE
WHERE rowno = 1
LAG returns a previous row in the result set as defined by the OVER
clause. If you just say LAG(col) that is the directly previous row.
The second argument specifies how many rows to go back, 1 in this case
(and, yes that is the default). The final argument specifies which
value to return when there is no previous row. Default: NULL.
LAG has twin function that operates in the opposite direction. The
name of that function is LEAD.

Neat. I've yet to play with 2012. Thanks for the example.

[i***@gmail.com]

I am using SQL 2008. I am able to get data for all counties using Partition By. However Candidate table has another column called PartyId(int FK). Party table has PartyId and PartyName. In the result we would also like to display the name of party. So the final list will have
CandidateName CountyName PartyName Lead

How do I join the result to party table so party name is also returned in list.

[Erland Sommarskog]

Post by i***@gmail.com
I am using SQL 2008. I am able to get data for all counties using
Partition By. However Candidate table has another column called
PartyId(int FK). Party table has PartyId and PartyName. In the result we
would also like to display the name of party. So the final list will
have
CandidateName CountyName PartyName Lead

So you would need to add PartyId to the PARTIION BY clause as well. The
join should not be any difficult to at all.

[i***@gmail.com]

I am working on SQL Server 2008. CandidateId is just a unique identifier. I am sorry I cant test query for SQL 2012 but the query Bob wrote returns correct results. However its returning me the result for only one county.