Archives of the TeradataForum
Message Posted: Mon, 17 Jul 2006 @ 21:14:54 GMT
| Subj: | | Re: How to get top N records |
| |
| From: | | Dieter Noeth |
Bhakti Ulhas Bongale wrote:
| | Is there any function, other than ranking, by which I can select the top N records of a result set? | |
Why don't you want to use ranking?
| | This query returns me say 2000 rows. I want only the top 50 rows. Is there any function similar to ROWNUM in Oracle that will give me
the expected result? | |
In V2R6.1 Microsoft's TOP syntax was implemented:
SELECT TOP 50 * FROM ...
But this is proprietary, a better way is RANK/ROW_NUMBER:
SELECT
...
QUALIFY
ROW_NUMBER() OVER (ORDER BY field1, field2, field3 DESC) <= 50
If you replace QUALIFY with a Derived Table & WHERE then it's Standard SQL and this is implemented in Oracle/DB2/MS SQL 2005,
too.
Dieter
| 
