Solved

Top Function in TeraData

Posted on 2006-06-22
29
12,737 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

626 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