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

Try Select Top 5 * from Tablle1 order by col1
Enthusiast

## Re: Top Function

Hi Aravind,

I hope you are working on V2R6.

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
Senior Apprentice

## 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?