Formula for saved search to identify unique record...
# generalg
G
09/11/2023, 5:26 PMFormula for saved search to identify unique records - my saved search is looking at the results at line level and I want to tag each unique record with a number going up to however many records there are on the search... i.e. record A has 3 lines, all three lines have 1, the record B has 5 lines, all 5 have 2 etc etc till the last record on the saved search which can be 40 or 4000.. is there a formula to do this?
d
David B
09/12/2023, 2:18 AMyes, use
DENSE_RANK()David B
09/12/2023, 2:20 AMfor example, if you had a transaction search that was ordered by descending internal id, the formula would look like:
DENSE_RANK() OVER (ORDER BY {internalid} DESC)g
G
09/12/2023, 3:32 PMthanks @David B, so what would I need to add within the brackets DENSE_RANK() for this to work? currently the results comes with
1d
David B
09/12/2023, 8:55 PMas much as I hate to say it, it's working for me:
David B
09/12/2023, 8:57 PMThere is an aggregate version of
DENSE_RANK()David B
09/12/2023, 8:58 PMread more about the analytic and aggregate forms of `DENSE_RANK`:
Oracle / PLSQL: DENSE_RANK Function
3 Views