All Forums Database
triskelarre 1 post Joined 10/13
03 Oct 2013
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
 

Raja_KT 1246 posts Joined 07/09
03 Oct 2013

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

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
03 Oct 2013

Hi,
Try the following:

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

 

Khurram

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

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

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

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@ir@m 35 posts Joined 05/13
08 Jan 2014

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

M.Saeed Khurram 544 posts Joined 09/12
08 Jan 2014

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

You must sign in to leave a comment.