Query with TOP and COUNT

Database

Query with TOP and COUNT

Hi,

I have a query, with a TOP 1000, but I need to know the Result Set total of the query. How can I do it?

Tks

6 REPLIES
Enthusiast

Re: Query with TOP and COUNT

Hi,

I think this is what you want....

SELECT location_Id

, prodid

, sales_amt

......

.....

FROM trans_fact

WITH SUM(sales_amt) BY location_Id

ORDER BY 1,2;

Cheers,

Raja

Enthusiast

Re: Query with TOP and COUNT

Hi,

Try the following:

SELECT TOP 10 col_name, 
COUNT(*) OVER () AS total_count
FROM TABLE_NAME;
Khurram
s_1
Enthusiast

Re: Query with TOP and COUNT

hi khurram,

what is the differece B/W TOP,SAMPLE FUNCTIONS

MECONOSAM LEVEL?

 

 

Ratnam

Enthusiast

Re: Query with TOP and COUNT

Hi Ratnam,

I have answered it on the other thread you have started:

http://forums.teradata.com/forum/database/top-function#comment-127912

Khurram
s_1
Enthusiast

Re: Query with TOP and COUNT

ok Khurram,

what is the internal functionality  top function ,

supose we fired a sql query " sel top 10 * from emp" 

emp table having 100 record, it's picking the top 10 records directly from base table and giving the user ?

(or)  

all table 100 records takeen to spool areya after filters top 10 records than giving the user ?

thanks

Ratnam

Enthusiast

Re: Query with TOP and COUNT

Ratnam,

Without order by clause the database will return any 10 rows from the table, but this result may not be truely random. The random function does extra processing to generate a random result.

AFAIK, The 100 rows will not be taken to spool, simple 10 random rows will be selected and returned. 

Khurram