Solved

Top Function in TeraData

Posted on 2006-06-22
29
12,596 Views
Last Modified: 2012-06-27
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!!!
0
Comment
Question by:jjrr007
  • 13
  • 11
  • 3
  • +1
29 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 16966380
I dont know if its possible for you but have u tried using querying your query?

0
 
LVL 4

Expert Comment

by:kenpem
ID: 16967388
If the query is just too complex to limit effectively, try wrapping it inside a VIEW, then SELECT TOP 10 * FROM theView
0
 
LVL 1

Author Comment

by:jjrr007
ID: 16967737
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
 
LVL 4

Expert Comment

by:kenpem
ID: 16967820
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
 
LVL 1

Author Comment

by:jjrr007
ID: 16967868
BTW, I don't have permission to change my permissions in the Teradata database and the database is not local.
0
 
LVL 1

Author Comment

by:jjrr007
ID: 16967879
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
 
LVL 4

Expert Comment

by:kenpem
ID: 16967895
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
 
LVL 12

Expert Comment

by:wstuph
ID: 16968352
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
 
LVL 1

Author Comment

by:jjrr007
ID: 16968658
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 16969144
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 16969153
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
 
LVL 1

Author Comment

by:jjrr007
ID: 16969268
The top function is not supported in Teradata.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16969308
is limit supported?

select f1
from table1
limit 3
0
 
LVL 1

Author Comment

by:jjrr007
ID: 16969347
If I can just get a row number added.  I can filter the dataset on the report level
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

by:jjrr007
ID: 16969366
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 16969403
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
 
LVL 12

Expert Comment

by:wstuph
ID: 16969450
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
 
LVL 1

Author Comment

by:jjrr007
ID: 16969583
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
 
LVL 1

Author Comment

by:jjrr007
ID: 16970730
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
 
LVL 1

Author Comment

by:jjrr007
ID: 16970750
The row function is above- it returns 1's.  Please advise.  This is needed urgently- thanks!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16970874
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
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 16970885
this may help u, I found a forum

http://www.teradataforum.com/l030103a.htm
0
 
LVL 1

Author Comment

by:jjrr007
ID: 16971128
I will try that forum.  Thanks.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16971160
I take it that SQL didnt work then

0
 
LVL 1

Author Comment

by:jjrr007
ID: 16971232
No, I couldn't get the row count thing to work.  
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16971365
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 16971371
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
 
LVL 1

Author Comment

by:jjrr007
ID: 16973201
I got the answer through the Teradat forum.  Thanks a lot rockiroads.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16974297
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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Read about achieving the basic levels of HRIS security in the workplace.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now