Archives of the TeradataForum
Message Posted: Sun, 17 Jul 2005 @ 09:10:03 GMT
<-- 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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 23 Jun 2019|