How do I write an SQL statement to return the 2nd highest value?

I need to write two seperate statements. One returns the max value from a
table and the other returns the 2nd highest value. Any help?

You can write one sql that will return the two highest values, sort the sql
desc and return the two top values

Select Top 2 ValueField From TableName Order by ValueField Desc
========================================



"Paul" wrote:

I need to write two seperate statements. One returns the max value from a
table and the other returns the 2nd highest value. Any help?

Thanks, Ofer. But the thing is, I actually have to write 2 seperate
statements. One to return JUST the max value, and another to return only the
2nd highest value. Any ideas?

"Ofer" wrote:

You can write one sql that will return the two highest values, sort the sql
desc and return the two top values

Select Top 2 ValueField From TableName Order by ValueField Desc
========================================



"Paul" wrote:

I need to write two seperate statements. One returns the max value from a
table and the other returns the 2nd highest value. Any help?

Is there any way that I can use that TOP 2 query in conjunction with a
deletion of the the MAX value? That would leave me with only the 2nd highest
value, right?

"Ofer" wrote:

You can write one sql that will return the two highest values, sort the sql
desc and return the two top values

Select Top 2 ValueField From TableName Order by ValueField Desc
========================================



"Paul" wrote:

I need to write two seperate statements. One returns the max value from a
table and the other returns the 2nd highest value. Any help?

Yes, when the top used as a filter.
try this:

DELETE TableName.*
FROM TableName
WHERE (((TableName.ValueField) In (select top 1 ValueField from TableName
order by ValueField desc)))



"Paul" wrote:

Is there any way that I can use that TOP 2 query in conjunction with a
deletion of the the MAX value? That would leave me with only the 2nd highest
value, right?

"Ofer" wrote:

You can write one sql that will return the two highest values, sort the sql
desc and return the two top values

Select Top 2 ValueField From TableName Order by ValueField Desc
========================================



"Paul" wrote:

I need to write two seperate statements. One returns the max value from a
table and the other returns the 2nd highest value. Any help?

Why two separate statements?

First Query.
Select Max(Something) as Maximum
FROM YourTable

Second Query.
SELECT Max(Something) as 2ndMaximum
FROM YourTable
WHERE Something
(SELECT Max(Something)
FROM YourTable)



Paul wrote:

I need to write two seperate statements. One returns the max value from a
table and the other returns the 2nd highest value. Any help?

I have a similar issue. I need to select the highest 5 test scores in a list
of test scores. However, some of the test scores may be the same for two or
more students. How do I get the list of the top 5 test scores like this:
95
91
91
86
84

Please let me know at
Thanks


"John Spencer (MVP)" wrote:

Why two separate statements?

First Query.
Select Max(Something) as Maximum
FROM YourTable

Second Query.
SELECT Max(Something) as 2ndMaximum
FROM YourTable
WHERE Something
(SELECT Max(Something)
FROM YourTable)



Paul wrote:

I need to write two seperate statements. One returns the max value from a
table and the other returns the 2nd highest value. Any help?

magicdds wrote:

I have a similar issue. I need to select the highest 5 test scores in a list
of test scores. However, some of the test scores may be the same for two or
more students. How do I get the list of the top 5 test scores like this:
95
91
91
86
84


SELECT TOP 5 score, student
FROM testscores
ORDER BY score DESC

You will get more than 5 only if there is a tie for fifth
place.

--
Marsh
MVP [MS Access]

try

SELECT TOP 5 TableName.ScoreField
FROM TableName
ORDER BY TableName.ScoreField DESC;

replace "TableName" and "ScoreField" with the correct names of the table and
the field.

note that "The TOP predicate does not choose between equal values. In the
preceding example [of TOP 25 grade point averages], if the twenty-fifth and
twenty-sixth highest grade point averages are the same, the query will
return 26 records."

hth


"magicdds" wrote in message
news
I have a similar issue. I need to select the highest 5 test scores in a
list
of test scores. However, some of the test scores may be the same for two
or
more students. How do I get the list of the top 5 test scores like this:
95
91
91
86
84

Please let me know at
Thanks


"John Spencer (MVP)" wrote:

Why two separate statements?

First Query.
Select Max(Something) as Maximum
FROM YourTable

Second Query.
SELECT Max(Something) as 2ndMaximum
FROM YourTable
WHERE Something
(SELECT Max(Something)
FROM YourTable)



Paul wrote:

I need to write two seperate statements. One returns the max value
from a
table and the other returns the 2nd highest value. Any help?

hello... i have a similar query, however i need to find the max value from
60%of the total entries...please help

"tina" wrote:

try

SELECT TOP 5 TableName.ScoreField
FROM TableName
ORDER BY TableName.ScoreField DESC;

replace "TableName" and "ScoreField" with the correct names of the table and
the field.

note that "The TOP predicate does not choose between equal values. In the
preceding example [of TOP 25 grade point averages], if the twenty-fifth and
twenty-sixth highest grade point averages are the same, the query will
return 26 records."

hth


"magicdds" wrote in message
news
I have a similar issue. I need to select the highest 5 test scores in a
list
of test scores. However, some of the test scores may be the same for two
or
more students. How do I get the list of the top 5 test scores like this:
95
91
91
86
84

Please let me know at
Thanks


"John Spencer (MVP)" wrote:

Why two separate statements?

First Query.
Select Max(Something) as Maximum
FROM YourTable

Second Query.
SELECT Max(Something) as 2ndMaximum
FROM YourTable
WHERE Something
(SELECT Max(Something)
FROM YourTable)



Paul wrote:

I need to write two seperate statements. One returns the max value
from a
table and the other returns the 2nd highest value. Any help?