jjrr007
asked on
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!!!
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!!!
rockiroads
I dont know if its possible for you but have u tried using querying your query?
If the query is just too complex to limit effectively, try wrapping it inside a VIEW, then SELECT TOP 10 * FROM theView
ASKER
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...
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...
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!
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!
ASKER
BTW, I don't have permission to change my permissions in the Teradata database and the database is not local.
ASKER
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.
Maybe the rank function will work. What is the syntax for thiat in Teradata? Thanks again.
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.
And can you paste the query at us here? Might be easier to solve this if we could see the context.
Yeah - paste up the query and let us take a look. I work with Teradata DBs every day, I can probably help you out.
ASKER
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
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
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
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
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
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
ASKER
The top function is not supported in Teradata.
is limit supported?
select f1
from table1
limit 3
select f1
from table1
limit 3
ASKER
If I can just get a row number added. I can filter the dataset on the report level
ASKER
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.
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
thats why I suggested if u can use a query within a query
this way the subquery acts as a table
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
?
column1 Column2
High 17
Next 15
Next2 14
....
Ten 2
?
ASKER
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
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
ASKER
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)
ROW_NUMBER() OVER (PARTITION BY E.column1 ORDER BY 2)
ASKER
The row function is above- it returns 1's. Please advise. This is needed urgently- thanks!
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I will try that forum. Thanks.
I take it that SQL didnt work then
ASKER
No, I couldn't get the row count thing to work.
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
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
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
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
ASKER
I got the answer through the Teradat forum. Thanks a lot rockiroads.
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
Would u mind posting it here so I can see plus for the benefit of others