Here are two shorter queries that do the same thing as your queries:
select distinct (select count(distinct number) from nMaxMin s2 where
s2.number<=s1.number) as rank, number from nMaxMin s1 order by 1
select number from nMaxMin s1 WHERE (select count(distinct number)
from nMaxMin s2 where s2.number>=s1.number)=5
Without indexes, these queries run slightly slower than yours.
But if we add an index, for example: CREATE INDEX ix ON nMaxMin(Number)
this queries will be faster.
Edited by - rsocol on 11/21/2003 02:48:10 AM