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

[Guest]

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?

 

[Guest]

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
========================================

 

[Guest]

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?

 

[Guest]

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?

 

[Guest]

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)))

 

[John Spencer (MVP)]

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)

 

[Guest]

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 (e-mail address removed)
Thanks

 

[Marshall Barton]

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.

 

[tina]

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

 

[Guest]

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

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

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 (e-mail address removed)
Thanks

 

[John Spencer]

Generically, that could look like the following.

SELECT Max(SomeField) as MaxValue
FROM (SELECT Top 60 Percent *
FROM SOMETABLE
ORDER BY SomeOtherField Desc) as TOP60

Or you might be able to use a subquery on the primary key of the table

SELECT Max(SomeField) as MaxValue
FROM SomeTable
WHERE SomeTable.PK IN
(SELECT Top 60 Percent PK
FROM SomeTable
ORDER BY SomeOtherField, YetAnotherField, PK)

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

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

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 (e-mail address removed)
Thanks


:

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?

 

[Guest]

thank you John... this has helped somewhat

Generically, that could look like the following.

SELECT Max(SomeField) as MaxValue
FROM (SELECT Top 60 Percent *
FROM SOMETABLE
ORDER BY SomeOtherField Desc) as TOP60

Or you might be able to use a subquery on the primary key of the table

SELECT Max(SomeField) as MaxValue
FROM SomeTable
WHERE SomeTable.PK IN
(SELECT Top 60 Percent PK
FROM SomeTable
ORDER BY SomeOtherField, YetAnotherField, PK)

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

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


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 (e-mail address removed)
Thanks


:

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?

 

[CMA]

I would like to use this in a modified fashion and need help.

I am looking at a set of data obtained by a query.

The Format is

Name1, Value1, Date1
Name1, Value2, Date2
Name1, Value3, Date3
Name2, Value4, Date4
Name2, Value 5,Date5

There are 2500 name records

I would like to make another table, containing unique Names, with record
information for the second highest date:

Name1, ValueX, Date(secondhighestdateofName1)
Name2, ValueY, Date(secondhighestdateofName2)

Help!

 

[Michel Walsh]

SELECT a.name, LAST(a.value), a.dateStamp
FROM yourTable AS a INNER JOIN yourTable AS b
ON a.name=b.name AND a.dateStamp <=b.dateStamp
GROUP BY a.name, a.dateStamp
HAVING COUNT(*)=2




The COUNT(*) supply the rank, here, by group, accordingly to the dateStamp.
So, we just pick the records where their (group's) rank is 2 (or second, if
you prefer).

Hoping it may help.
Vanderghast, Access MVP

 

[CMA]

Thank You!

This certainly did what I wanted it to do, and I appreciate your help.

Might you kindly take a minute so that I actually understand HOW it worked
(I am violating a commandent of MS Access Newsgroups - copying and pasting
code without understanding it).

So my table(query) was joined to itself, cfreating another table with every
possible combination of info (The first Name1 recored was joined to all other
records involving Name1) However, this table was also joined by the dates,
taking dates from one table that were smaller than the rest - and the
count(2) was a limiting factor of some sort?

I don't think I fully understand....

I really thank you for the immediate help.

 

[Michel Walsh]

Consider that a and b are two of your fingers running over a single
(vertical) list. For a given position of finger_a, finger_b can run over all
the list BUT must be such that it is on a record with the same 'name' as the
one for finger_a:

finger_a.name = finger_b.name


and ALSO, finger_b can only 'stops' or 'points' on a record such that

finger_a.dateStamp <= finger_b.dateStamp


Given those conditions, COUNT the number of records finger_b can 'legally'
stops over. If that count is =1, then finger_a is, for finger_a.name, on
the record with the highest value for dateStamp (for that name). If the
count=2, then you have the second record, ordered by dateStamp, for that
'name', which is what you want.


Vanderghast, Access MVP

 

[CMA]

Cool.

I almost get that.....

The count function then "ranks" the data, and we are choosing the rank? And
what of the "LAST" criteria used in the code?

(Reply by your choice.... I have what I need, and also just like learning
and understanding)

 

[Michel Walsh]

Yes, the statement ranks the original { a.name, a.dateStamp } values by
name: for a given a.name, the dateStamp get its rank within all the original
records having the same name: 1 (first), 2 (second) , 3, ... such as:


name date rank=COUNT(*)
john 2001.01.01 1
mary 2001.01.01 1
mary 2005.01.01 3
john 2006.01.01 2
mary 2002.01.01 2 <- (1)
mary 2008.01.01 4


(1) for all records with name = Mary, this record is the second (rank =2)
one, if those said records are ordered in increasing order of their date.
Note that a.dateStamp <=b.dateStamp or a.dateStamp >= b.dateStamp
defines the order (descending or ascending) used to establish the ranking
value.




Since COUNT(*) is the rank, and since we want the second of each group, we
need the condition:

COUNT(*)=2


and since COUNT(*) has to be computed, it has to be involved in an HAVING
clause, since a WHERE clause is used BEFORE any aggregate is computed. So


HAVING COUNT(*)=2


is what we were needing. WHERE COUNT(*)=2 would produce an error.






Vanderghast, Access MVP

 

[Michel Walsh]

Note that the syntax works only if there is no duplicated records when
looking only at the fields implied in the GROUP BY. If that condition is not
respected, we have to use a sub query to compute the rank (which is probably
even slower than the join).


Vanderghast, Access MVP

 

[Michel Walsh]

About the LAST aggregate in the SELECT statement?

Given GROUP BY a.name, a.dateStamp, then a.value is uniquely determined,
so, instead of

SELECT a.name, a.dateStamp, a.value, ...
....
GROUP BY a.name, a.dateStamp, a.value


I used


SELECT a.name, a.dateStamp, LAST(a.value), ...
....
GROUP BY a.name, a.dateStamp



but you could have used MIN, or MAX (but not SUM, neither COUNT). The idea
is to pick one record value, for a record in the group { a.name,
a.dateStamp }, any record in the group will do, so I used LAST, instead of
MIN, because if I need to pump a second field from the SAME record, I just
write:

SELECT a.name, a.dateStamp, LAST(a.value), LAST(a.secondField) ...
....
GROUP BY a.name, a.dateStamp


and I am sure that the SAME record (but any record of the group) supply both
a.value and a.secondField. That would *not* be sure if I used MIN, or MAX.




Vanderghast, Access MVP