I need to conduct a ranking function in Trifacta that works similar to the RANK() function in T-SQL. https://docs.microsoft.com/en-us/sql/t-sql/functions/rank-transact-sql?view=sql-server-2017

[TrifactaUsers]

Attached is an example where I want to group by the load_dt column and order by the load_tm column in descending order by compute the values in the ranking column.

?

[Trifacta_Alumni]

Hi Adrian,

One way that you can rank in Trifacta is by using the ROWNUMBER() function. One discrepancy is that when there are duplicates, it's not going to behave in the manner your example shows, so it's best to de-duplicate the values before using Rownumber. Here's what it looks like before deduplicating:

And here is what it looks like after deduplicating:

Sorting by -load_tm ranks by descending order, and grouping by last_dt causes the rank to start over for each dt.

Does this make sense and does it answer your question?

Best,

David

[TrifactaUsers · Accepted solution]

Thank you I was able to compute the ranking by applying it at the Dataset level.  I used this HQL function:

RANK() OVER(PARTITION BY tst_pln_id ORDER BY load_dt DESC,load_tm DESC) AS ranking

[4a89899d57c377372170]

Hi Adrian,

How do we use the substitute for Rank at dataset level especially for Hive Table dataset:

Thank you I was able to compute the ranking by applying it at the Dataset level. I used this HQL function:

RANK() OVER(PARTITION BY tst_pln_id ORDER BY load_dt DESC,load_tm DESC) AS ranking

Regards,

Prash