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

Hi,
 
what is the differece B/W TOP,SAMPLE FUNCTIONS
MECONOSAM LEVEL?
 
 
Ratnam

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.

 

Cheers,

Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
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.
 
 

Khurram

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.
Btw, what is MECONOSAM LEVEL?

Dieter

You must sign in to leave a comment.