All Forums Database
MattW At ATT 11 posts Joined 06/06
31 Jul 2006
Top Function

Is there an equivilant to the Top function in Teradata?MS syntax select top 5 * from TABLEX order by 1

vinod_sugur 22 posts Joined 04/05
01 Aug 2006

There is an Top function in V2R6, but if you want to try out in V2R5 you need to go by analytical function.Select *From vinod_1Qualify Row_number() OVER(Order by empno) <= 5I hope this helps.

MattW At ATT 11 posts Joined 06/06
03 Aug 2006

This worked great. Thanks for the help. It's a bit of a spool hog, but that's another issue entirely...

aravind-3019 39 posts Joined 08/06
04 Aug 2006

Teradata also supports TOP function.Try Select Top 5 * from Tablle1 order by col1

vinod_sugur 22 posts Joined 04/05
04 Aug 2006

Hi Aravind,I hope you are working on V2R6.

s@ir@m 35 posts Joined 05/13
23 Dec 2013

what is the differece B/W TOP,SAMPLE FUNCTIONS

Raja_KT 1246 posts Joined 07/09
23 Dec 2013

Hi Ratnam,

Sample mainly for getting samples of row from table or view. We can use SAMPLE n, where n is an integer or can be decimal between 1.00 and 0.00. We can also use SAMPLEID.


TOP n operator shows number of rows or percentage of rows from  result set.

eg : SELECT TOP 10 PERCENT * FROM employee;


You may use Qualify ..row_number()  too to restrict rows.




Raja K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

M.Saeed Khurram 544 posts Joined 09/12
24 Dec 2013

Hi Ratnam,
In a Sample n function, the amps will choose rows in some random fashion. So there is no need to sort the rows.
In TOP n function the rows are sorted first and then the top n rows specified are returnd. 
You can use random if you want to get a truely random result.


dnoeth 4628 posts Joined 11/04
24 Dec 2013

Hi Ratnam,
SAMPLE (RANDOMIZED ALLOCATION) returns a truely random result while TOP n (without PERCENT/ORDER BY) simply returns the first n rows found on a single AMP (or multiple AMPs). SAMPLE is always more overhead/slower compared to TOP (without PERCENT/ORDER BY)
If you just want to see some data better use TOP, if you do some statistical stuff switch to SAMPLE.


You must sign in to leave a comment.