Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 21 May 2004 @ 08:55:43 GMT


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


Subj:   Re: Select TOP 10 * ..
 
From:   Dieter Noeth

Sonny Chee wrote:

  Anyone know of a way to get Teradata to just return just the first X number of rows? I know queryman will ask you after retrieving 2000 rows if you want to continue and get the rest but I'm looking for a way to specify the number of rows I want in the SQL. In MS SQL Server for example one types,  


     Select top 10 *
     From MyTable
     Order by MyTable.Col1

     select *
     from MyTable
     qualify rank() over (Order by MyTable.Col1) <= 10

But be aware, that this will retrieve a result set similar to TOP WITH TIES. If you want exactly 10 rows regardless of ties you have to add more columns to the Order cluase to make it unique

or use

     "sum(1) over (Order by MyTable.Col1 rows unbounded preceding)"
     or (if you're on V2R5) use
     row_number() over (Order by MyTable.Col1)

Dieter



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 23 Jun 2019