How do I get the second highest value?

[dabbler]

Hi guys/gals, I need to write a query which finds out the second highest value for a company.

I have two tables, "A" and "B". A stores company names, and B holds the sales and each value.

The tables are linked by A.COID =B.COID.

What I need is the second highest sale for each company. naturally select max(b.value) returns the highest sale, so how do I manipulate this to get the second highest?

If you have any bright ideas, please send them on a postcard to... ;-)

 

[Daxton A.]

Reply

Well if you sorted the table Descending. Wouldnt the second highest fall the row under that one.

 

[dabbler]

Yes but you would have to bring back all values, whereas I only want the second.

 

[Daxton A.]

REPLY

It seems like you know what you talking about and all and probably more than i do. But it just seems to me as if you would have to Find the Highest Value from the Sorted Company table and then extract out the Record Under that.

I dont know how else to do it other than Find the highest and remove it and find the higest again.

Daxton A

 

[FoFa]

The tables are linked by A.COID =B.COID.
What I need is the second highest sale for each company. naturally select max(b.value) returns the highest sale, so how do I manipulate this to get the second highest?

Select max(B.value)
from B
inner join A on A.COID = B.COID
where B.value <> (Select max(B.value) from B as B2 where B2.COID = B.COID)

Subselect works good in this instance

 

[tihomir]

just try something like this

SELECT min(YourCol) FROM YourTbl
WHERE YourCol IN (
SELECT TOP 2 YourCol from YourTbl);

It must work.

 

[tihomir]

Corection:

SELECT min(YourCol) FROM YourTbl
WHERE YourCol IN (
SELECT TOP 2 YourCol from YourTbl ORDER BY YourCol DESC);

 

[Jon K]

Select the second highest sale value for each company


Try this query (type/paste in the SQL View of a new query, using the correct table names and field names):-

SELECT .COID, [A].[company name], .Value
FROM [A] INNER JOIN ON [A].COID=.COID
WHERE (Select Count(*) from as S where Value >= .Value AND COID = .COID) = 2
ORDER BY .COID;


The query uses a subquery to rank the sale values of each company in descending order and select the rank #2.

 

[dabbler]

Thanks lads - top answers!