Top Function in TeraData

If the query is just too complex to limit effectively, try wrapping it inside a VIEW, then SELECT TOP 10 * FROM theView

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

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!

BTW, I don't have permission to change my permissions in the Teradata database and the database is not local.

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.

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.

Yeah - paste up the query and let us take a look.  I work with Teradata DBs every day, I can probably help you out.

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

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

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

The top function is not supported in Teradata.

is limit supported?

select f1
from table1
limit 3

If I can just get a row number added.  I can filter the dataset on the report level

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

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

?

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

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)

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

I will try that forum.  Thanks.

I take it that SQL didnt work then

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

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

I got the answer through the Teradat forum.  Thanks a lot rockiroads.