Re: sql

If you're on 8i 8.1.6 or later, you can use analytic functions for this (either rank() or dense_rank() depending on what you want to do if two or more values qualify for second maximum - rank() will assign the same rank to all of them and SKIP several consecutive values to accommodate for matching values, while dense_rank() will not skip any values - for the case of second max I think it doesn't matter which ranking function you will use.)

For example,

SELECT /* DISTINCT to get just one value if there are matches */

           val
 FROM ( SELECT

                       val
                      ,rank() OVER (ORDER BY val DESC) rank
            FROM
                    my_table
         )
 WHERE
            rank = 2

Of course, there are other ways to do this - for example, in 9i you could write your own aggregate function that returns second max. http://asktom.oracle.com has an example of just such user aggregate function if I am not mistaken.

hth.

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"abhishek" <singhhome_at_yahoo.com> wrote in message
news:e52380ab.0209232324.24bd2cbd_at_posting.google.com...


> how  to write sql querry to

> 1. show the second maximum value of a column.