how we can get the second highest record in the table . for example I have
table "StudScores" with fields (studID int,Studname,StuDScores)

I wants the query to find out Record in the "StudScores"  table of  the
Student which  has second highest score

Untested, but something like this should work:

SELECT TOP 1 s.StudID, s.StudName, s.StudScores
FROM StudScores AS s
WHERE s.StudScores <
  ( SELECT MAX(s1.StudScores)
    FROM StudScores AS s1 )
ORDER BY s.StudScores

Ben

Think set theory next time and the answers just given will pop out.

Show quoteHide quote

you could also try this:

select top 1 * with ties
from (select top 2 * with ties
from studscores
order by studscores desc)
order by studscores asc

Show quoteHide quote

Rows are not records, and columns are not fields.

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.  It is very hard to debug code when you do not let us
see it.

Student which  has second highest score  <<

There are many ways to do this.  Here is a pure ANSI version:

SELECT stud_id, stud_name, stud_score
FROM StudScores AS S1
WHERE  S1.stud_score
          = ( SELECT MAX(S2.stud_score)
                 FROM StudScores AS S2
               WHERE stud_score
                          < (SELECT MAX(S3.stud_score)
                                    FROM StudScores AS S3));

ORDER BY s.StudScores

This approach would require higher levels of nesting of subqueries if the OP
wanted 3rd highest, 4th highest etc. For an ANSI version, I would recommend
using the logic behind the usual ranking method, which is much simpler and
efficient.

This one is due to Marc Sokol and he did not go beyond three levels.
DB2 keeps the top two values in the Stats to make a rough histogram of
the distribution using five values (top two, average and lower two).
It is one of these weird implementation tricks.

Scope of temp table
Why is Count(*) slow?
Bad datetime values stored by SQL Server 2000
Bitwise operations on BigInt
Upsized SQLServer app 100x slower than MsAccess