Re: TOP TEN LISTS? HELP.

In article <3cmj5cINN7sd_at_news.ougf.fi>, rissahe1_at_news.ougf.fi (Heikki Rissanen) writes:
> mwmaher_at_ACM.ORG wrote:
> : How do you create a listing of the top N items in a table
> : with straight SQL? This seems like such a basic task -
> : until you try it.
>
> This is not very effective and cannot be recommended with large
> tables and bad indexes, but something like this may work:
>
> SELECT FLD
> FROM TAB1 A
> WHERE 10 >= ( SELECT COUNT(*) FROM TAB1
> WHERE TAB1>A.FLD)
> ORDER BY FLD DESC
>
> Regards,
> Hessu
>
> --
> ---------------------------------------------------------------------
> Heikki Rissanen, United Papermills, Rauma, Finland
> Email: heikki.rissanen_at_ra.upm.yhtyneet.mailnet.fi
> ---------------------------------------------------------------------

You can also use the INDEX_DESC hint in version 7:

• Create an index on your key column
• Select your key column using the index in descending order:

   Your query should be like:

	select key_column /*+ index_desc(table_name index_name) */
	from table_name
	where rownum <= 10
	and key_column < 10000000000  <--- arbitrarily large number

   Notes: 
	- the syntax of the hint should be *exactly* as shown!
	- the last condition is a dummy condition to force the use of the index

(See page 5-21 of the Application Developer's Guide for more info about the index_desc hint)

Pierre Hollard
z52289_at_mip.lasc.lockheed.com Received on Thu Dec 15 1994 - 15:20:13 CET