Top Function

Database
Enthusiast

Top Function

Is there an equivilant to the Top function in Teradata?

MS syntax

select top 5 * from TABLEX order by 1
8 REPLIES
Enthusiast

Re: Top Function


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_1
Qualify Row_number() OVER(Order by empno) <= 5

I hope this helps.

Enthusiast

Re: Top Function

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

Re: Top Function

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

Re: Top Function

Hi Aravind,

I hope you are working on V2R6.

s_1
Enthusiast

Re: Top Function

Hi,

what is the differece B/W TOP,SAMPLE FUNCTIONS

MECONOSAM LEVEL?

Ratnam

Enthusiast

Re: Top Function

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

Enthusiast

Re: Top Function

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
Junior Contributor

Re: Top Function

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?