Archives of the TeradataForum

Message Posted: Sun, 17 Jul 2005 @ 09:10:03 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Thursday, July 14, 2005 09:35 -->

I've see a couple of solutions for this. I think the original didn't really ask about a top(n) function. But, you can mimic this Functionality by doing something like the following:

     SELECT CountryID
           ,CountryName
           ,SUM(1)   OVER   (ORDER BY CountryName
                             ROWS UNBOUNDED PRECEDING)    AS R_NBR

     FROM developer_db.CountryBase

     QUALIFY R_NBR <= 10;

The previous solution provided using the DBC.Tables ignores the fact that most users do not and probably should not have access to the DBC.Tables. A view, with row level access locks, of the DBC tables for developers is okay though.

Be aware that the above OLAP and other OLAP functions (like RANK) each may be used to get the Top(n) rows. Each have their own constraints and limits. Rank in particular may return more rows than you expect because two rows can have the same rank. Also be aware that TD will probably do an all-rows scan and it may redistribute the data in the order by to determine the order. It may return a small amount of data, but it will do all the work as if you wanted all the data.

	<Prev	Next>		<<First	<Prev	Next>	Last>>