Hello,
We're trying to develop a "customer track" for WordFly emails that gives content to customers based on their interests. Right now we have a procedure that assigns weight to interest based on the count of distinct perf no's containing the tkw id. This data lives in TX_CUST_TKW. What I'm trying to do (and what is probably pretty easy to do for someone with more advanced SQL knowledge) is get the highest weighted tkw from the constituent record, assuming it falls in one of two tkw categories (genre and subgenre, categories for the keywords assigned to perfs). I'm using MAX function, but I keep getting the value for all the tkw's that are selected and weighted on the constituent record. I just want the highest value for each constituent.
What I'm getting:
customer_no description weight
1 Opera 5
1 Dance 10
1 Theater 30
2 Opera 50
2 Dance 15
What I want:
Hopefully this can be chalked up to my intermediate (at best) SQL skills, and someone can point me in the right direction. Thanks in advance!
Michael
I believe you're going to need to use RANK for this.
Thank you, Gawain Lavers! You've pointed me in the right direction and helped me learn something new and useful.
Happy New Year to you!
Another option is to use the ROW_NUMBER function. Basically, same concept.
If you put it in a CTE, you can then select only the top rank/row_number.
Good luck with it…RANK isn't the most efficient thing, so you'll have to factor that into your usage. And a Happy new Year to you as well!