Re: Second highest value

jegatheesan wrote:
>
> Try using this command
>
> select max(data) from temp where data < (select max(data) from temp)
>
> By,
> jegatheesan.A
> --
> Posted via Talkway - http://www.talkway.com
> Exchange ideas on practically anything (tm).

Consider this:

SQL> CREATE TABLE t (n NUMBER);

Table created.

SQL> INSERT INTO t VALUES (1);

1 row created.

SQL> INSERT INTO t VALUES (2);

1 row created.

SQL> INSERT INTO t VALUES (3);

1 row created.

SQL> COMMIT; Commit complete.

Now I have a test table. I'm going to try your solution to get the second highest value.

SQL> SELECT MAX(n)
  2 FROM t
  3 WHERE n < (

  4      SELECT MAX(n)
  5      FROM t);

   MAX(N)

        2

OK. This works fine. But what if I want to get third highest value. I'll do like was suggested.

SQL> SELECT MAX(n)
  2 FROM t
  3 WHERE n < (

  4      SELECT MAX(n)
  5      FROM t
  6      WHERE n < (
  7          SELECT MAX(n)
  8          FROM t));

   MAX(N)

        1

Bingo!

But imagine if I had a table with 239 002 records and I desperately need to sort out a record, which by some kind of criteria has the 3674-th highest value. If I kept using the same method, then writing the query is harder than 10 jobs of Hercules and before getting results from database I would be eating icecream in heaven.

It is hard to me to believe, that there is no more decent way to query out the x-th highest value from database than this. Can somebody share his knowledge about how this can be done.

--
Ilusat suve...

    \ | /
     \ | /
-----( )-----

     / | \
    / | \

...ja päikest :)

Juhan Received on Wed Aug 11 1999 - 08:28:11 CDT