All Forums Database
c@lib3ar 7 posts Joined 10/09
23 Oct 2009
rank() (partition by....) help

i am trying to come up with a way to rank and have the ability to window using the rows function. i know that neither the rank nor the percent_rank functions allow you to specify the rows to window over.so what i would want to do if using rows was possible in the rank statement:rank() over (partition by column order by column )does anybody know of a work around for this?

27 Oct 2009

Hi,WHat you could use is the PRECEEDING or PROCEEDING function call in the Partition to SELECT the # of rows you desire. Example, if you are ranking, then you can use a filter for Rank #1 and the Proceeding 10 rows to window over 10 rows only. I hope that makes sense, or I have completely lost your question. Get in touch if you need help.

c@lib3ar 7 posts Joined 10/09
27 Oct 2009

thanks. that is exactly what i want to do, but it is not allowed within the rank function. i just noticed the forum cut off some of my code. i wanted to bold it, but instead it got deleted. the *** part is what i want to do, but cant in teradata.rank() over (partition by column order by column ***rows between 200 preceding and current row***)

Jimm 298 posts Joined 09/07
27 Oct 2009

Like Arun, I thought you were talking rubbish! But you are correct - you cannot use Rows on a Rank function.The following will find the 200 preceding rows for every partition column in a subquery and then Rank them. :-Select PKCol, PartCol, RankCol, Rank () Over (Partition By PartCol Order By RankCol) As RowOrderFrom (Select PKCol, PartCol, RankCol, Row_Number () Over(Partition By PartCol Order By RankCol Desc ) As RowLimitFrom TDUSER.T1Qualify RowLimit LE 200 ) D1Order By 2,4 Desc;I also did not think you could use an Olap function in a derived table and then use Olap on that. So a very good day - ThanksJim M

c@lib3ar 7 posts Joined 10/09
27 Oct 2009

thanks jim! so there were actually 2 parts to the question. i was hoping that would help with both, but alas i am still having an issue. the second part is more complicated, but is the same general problem. lets say i had the following data set; i want to order by time, then look back 5 rows and find the relative rank (create the fake_rank column) for each row. the order by time aspect is killing me and there is no real partition this time around. im not sure it is possible, but was hopeful someone here might have run into something similar. thanks again. time/price/fake_rank (desc)1/6/?2/9/?3/5/?4/4/?5/7/26/10/17/3/5 8/6/39/9/210/5/4

Jimm 298 posts Joined 09/07
28 Oct 2009

See below:- Create Volatile Table Tst1("Time" Smallint Not Null, Price Smallint Not Null)Unique Primary Index ("Time")On Commit Preserve Rows;Insert Into Tst1 Values (1,6);Insert Into Tst1 Values (2,9);Insert Into Tst1 Values (3,5);Insert Into Tst1 Values (4,4);Insert Into Tst1 Values (5,7);Insert Into Tst1 Values (6,10);Insert Into Tst1 Values (7,3);Insert Into Tst1 Values (8,6);Insert Into Tst1 Values (9,9);Insert Into Tst1 Values (10,5);Select "Time", Price, Rank() Over (Order By Price Desc) As Fake_RankFrom (Select "Time", Price, Rank() Over (Order By "Time" Desc ) As Faker_RankFrom Tst1Qualify Faker_Rank LE 5) As D1Order By 1;----------------------------------Gives: Time Price Fake_Rank 6 10 1 7 3 5 8 6 3 9 9 2 10 5 4----------------------------------Just a slight variation on the previous answer.Note that Time 5 in your suggested dataset does not match - a typo in the dataset or you have not specified your rules fully?

28 Oct 2009

Hi,Yes you are right. You cannot use the PRECEEDING/PROCEEDING function on a rank. But you can use it on an AGGREGATE function call from the Partition function. What you can do too is to ensure your GROUPING columns get full row identifiers and then use the SUM function to get PRECEEDING/PROCEEDING number of rows from your call. I have done this before and it works fine AS LONG AS you make sure that you not overlap rows of wanted records by not specifying the full key identifier in your Partitioning function call. If you need any further help, get in touch. I don't know if I can currently give you exact Syntax as I do not have access to a Teradata Server to run test queries :). Take care and hope this helps.

c@lib3ar 7 posts Joined 10/09
03 Nov 2009

thanks guys. sort of combined the two solutions to get to what i was wanting. appreciate the help.

You must sign in to leave a comment.