Re: sql

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Tue, 24 Sep 2002 13:43:40 +0400
Message-ID: <ampc78$of$1@babylon.agtel.net>

Nope. Due to nature of rownum, this query will never return any rows. Something like this would work:

select min(val) second_max from
(select val from
(select val from my_table order by val desc) where rownum <= 2
)

(by the way, this seems to be more effective query than that I suggested in my other post, using rank() function - but maybe that's just my particular layout...)

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


"Tino Korth" <newsgroup_at_tk79.de> wrote in message news:ampa8m$3tp$2_at_nntp-m01.news.aol.com...


> Hello,

> 

> 

> abhishek schrieb:

>  > how  to write sql querry to

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

> 

> I think that will work ...

> 

> SELECT * from (SELECT * from MyTable ORDER by RowName) WHERE ROWNUM=2

> 

> 

> Greetings from Rostock, Germany!

> 

> Tino

> 

> -- 

> Tino Korth                               Registered Linux User #286417

> www.tinokorth.de - ICQ 10771191                  http://counter.li.org

>

Received on Tue Sep 24 2002 - 04:43:40 CDT