how to generate row_number in tmap

[ffanali0804]

hello,

Is there a solution to use this row_number in a tMap?

SELECT AccountID,

UserTagID,

Status,

LogDate,

"row_number"() OVER (PARTITION BY UserTagID, AccountID, LogDate ORDER BY LogDate DESC) rn

FROM log_user_tag

Thank you so much,

Federico

[Anonymous]

You can certainly add row numbers in the tMap, but if you are trying to replicate this query, why don't you just use this query in your DB component?

[Anonymous]

Take a look at sequence of the Numeric routine: https://help.talend.com/r/en-US/8.0/studio-user-guide/numeric-routines

We can do a partition like this:

Numeric.sequence( row1.UserTagID+"__"+row1. AccountID+"__"+row1.LogDate , 1, 1)

ORDER BY I'm afraid is only possible if you add a tSortRow to your flow.

[ffanali0804]

I had actually tried this solution but I had doubts about the performance, so I wanted to compare it with the one with the tmap. If you confirm that they are similar then I would use the one you are proposing directly 🙂

thanks

[Anonymous]

You will NEVER outperform a DB manipulating data like this, with any other application that has to receive the data, then sort it. This is what databases are for. If you have all of your data in a single DB, you may as well carry out any sorting or calculations that can be carried out in the DB, in the DB. Then send it to other applications to be further processed.

[Anonymous]

Databases are made to run queries super fast. We're trying to maninpulate data that is not in the right order, etc. It can't be that efficient.