Top Function in TeraData

I am having trouble getting the top 10 rows in Teradata.  

This is been complicated by a couple things:
1. I don't have permissions to create a temporary table.
2. I am using a group by function in the query.
3. I am joining to other tables in the query.

I have tried to use sample but have had problems because of joining to other tables.  Maybe, I don't have the syntax correct- I just put it right before the from clause.  Also, I tried the cum function, but had problems because I am using a group by.  

I have asked a question similar to this before; however, this time I have the complicated factors mentioned above.

Thank you!!!
LVL 1
jjrr007Asked:
Who is Participating?
 
rockiroadsConnect With a Mentor Commented:
this may help u, I found a forum

http://www.teradataforum.com/l030103a.htm
0
 
rockiroadsCommented:
I dont know if its possible for you but have u tried using querying your query?

0
 
kenpemCommented:
If the query is just too complex to limit effectively, try wrapping it inside a VIEW, then SELECT TOP 10 * FROM theView
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
jjrr007Author Commented:
rockiroads
How do I query the query?  Please let me know the syntax for this...

Kenpem,
I don't know how to do what you suggested either.  Please let me know the syntax for this...

0
 
kenpemCommented:
bear in mind I don't actually KNOW TeraData, so this is based on standard MS T-SQL Syntax:

if we represent your big complex query as "SELECT BLAH FROM BOO" (I know there must be a lot more!):

CREATE VIEW MakeItEasy AS SELECT BLAH FROM BOO
GO

then you should be able to

SELECT TOP 10 * FROM MakeItEasy

hope that TeraData is close enough to MS that this works for you!
0
 
jjrr007Author Commented:
BTW, I don't have permission to change my permissions in the Teradata database and the database is not local.
0
 
jjrr007Author Commented:
I don't think that I have permission to create a view.  I will double check and let you know.  

Maybe the rank function will work.  What is the syntax for thiat in Teradata?  Thanks again.
0
 
kenpemCommented:
Any chance your permissions to be extended to something more suited to a developer?

And can you paste the query at us here? Might be easier to solve this if we could see the context.
0
 
wstuphCommented:
Yeah - paste up the query and let us take a look.  I work with Teradata DBs every day, I can probably help you out.
0
 
jjrr007Author Commented:
I can't chage the permissions.  Kenpem, I don't have permission to create a view.

The query is below,  I replaced the propietary information with the corresponding column table/names.  It's:
Select
E.Column1
Count(E.Column1) AS Column2

From
Table1 F Inner Join Table2 A ON
F.Column3= A.Column3

Inner Join Table4 E ON
A.Column5 = E.Column5

Group By
E.Column1

Where
(A.Date Between '2006-01-01' AND '2006-01-31')

Order By 2 DESC
0
 
rockiroadsCommented:
What are u using to query Teradata?

Reason why I ask is that whatever u use may support querying a query
e.g.

SELECT TOP Column2
FROM (Select E.Column1, Count(E.Column1) AS Column2
          From Table1 F Inner Join Table2 A ON F.Column3= A.Column3
          Inner Join Table4 E ON A.Column5 = E.Column5
          Group By E.Column1
          Where (A.Date Between '2006-01-01' AND '2006-01-31'))
Order By Column2

0
 
rockiroadsCommented:
I forgot the number in TOP
select top 3


not sure if top is supported with what u are doing

this is access

oracle would be rownum

so not sure what teradata is

0
 
jjrr007Author Commented:
The top function is not supported in Teradata.
0
 
rockiroadsCommented:
is limit supported?

select f1
from table1
limit 3
0
 
jjrr007Author Commented:
If I can just get a row number added.  I can filter the dataset on the report level
0
 
jjrr007Author Commented:
No the limit function is not supported.  The rank function will not work because I am using an aggregate function- i.e. taking an aggregate of an aggregate.
0
 
rockiroadsCommented:
rank Im assuming is what u use to get your rows?
thats why I suggested if u can use a query within a query
this way the subquery acts as a table
0
 
wstuphCommented:
I'm still trying to figure out what the result you're looking for is, personally.  So you want, say, the top 10 Column2 counts with the Column1 names?

column1 Column2
High  17
Next  15
Next2 14
....
Ten   2

?
0
 
jjrr007Author Commented:
rockiroads
A subquery would be a good idea- the only problem is that you can't join in a subquery in Teradata

wstuph,
The query is suppose to get the top 10 counts-i.e. Count(E.Column1)


Again, having a row count function would be ideal...
I pasted my query here again, since this question is getting long:

Select
E.Column1
Count(E.Column1) AS Column2

From
Table1 F Inner Join Table2 A ON
F.Column3= A.Column3

Inner Join Table4 E ON
A.Column5 = E.Column5

Group By
E.Column1

Where
(A.Date Between '2006-01-01' AND '2006-01-31')

Order By 2 DESC
0
 
jjrr007Author Commented:
I found the function for row_number in Teradata.  It is still not working.  I just return 0's please advise in the third column.  What is wrong.  If we can get this last column to work- the query should be OK.  Thanks

ROW_NUMBER() OVER (PARTITION BY E.column1 ORDER BY 2)
0
 
jjrr007Author Commented:
The row function is above- it returns 1's.  Please advise.  This is needed urgently- thanks!
0
 
rockiroadsCommented:
ok, lets try this then


Select
E.Column1
Count(E.Column1) AS Column2

From
Table1 F Inner Join Table2 A ON
F.Column3= A.Column3

Inner Join Table4 E ON
A.Column5 = E.Column5

Group By
E.Column1

Where
(A.Date Between '2006-01-01' AND '2006-01-31')

QUALIFY ROW_NUMBER() OVER (ORDER BY E.Column1) < 4

0
 
jjrr007Author Commented:
I will try that forum.  Thanks.
0
 
rockiroadsCommented:
I take it that SQL didnt work then

0
 
jjrr007Author Commented:
No, I couldn't get the row count thing to work.  
0
 
rockiroadsCommented:
In your query where did u use RANk?

Select
E.Column1
Count(E.Column1) AS Column2

From
Table1 F Inner Join Table2 A ON
F.Column3= A.Column3

Inner Join Table4 E ON
A.Column5 = E.Column5

Group By
E.Column1

Where
(A.Date Between '2006-01-01' AND '2006-01-31')

HAVING RANK (E.Column1)

Order By 2 DESC


0
 
rockiroadsCommented:
Im trying, honest guv. Since I dont have teradata, its a touch tricky to try help u with regards to myself testing first

Ok, Im doing a google search
http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLG,GGLG:2005-39,GGLG:en&q=ROW%5FNUMBER%28%29+teradata

and from there, this link looks promising
http://www.teradata.com/t/go.aspx/?id=44353

this is using a subquery like the one I mentioned, so perhaps u can use the example here and try?
on the outer query, use RANK

0
 
jjrr007Author Commented:
I got the answer through the Teradat forum.  Thanks a lot rockiroads.
0
 
rockiroadsCommented:
No probs, glad you managed it. Tough one eh.
Would u mind posting it here so I can see plus for the benefit of others
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.