Re: top N or rewriting MAX

otierney_at_freenet.edmonton.ab.ca wrote:
> Does anyone know of good (ie FAST) ways of getting the top
> N rows in a table?

This is actually quite simple. This example assumes you have a table MY_TABLE with a numeric column VALUE. The SELECT statement will give you the two lowest values.

SELECT t1.value
FROM my_table t1, my_table t2
WHERE t1.value <= t2.value
GROUP BY t1.value
HAVING COUNT (*) > (SELECT COUNT (*) - 2 FROM my_table);

I don't know how it performs on large tables but it seems to work. I haven't used it in any of my own applications but the problem interests me. :-)

/Roger

-- 
Sport radio: people listening to people watching people having fun
Mailto:roger.wernersson_at_adra.se
BTW: All opinions are mine, all mine, and nobody's but mine.