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!
Thanks Neil, ROW_NUMBER was what I was actually thinking about! RANK is problematic for this because it can give you ties, so you might wind up with two or more "1" values.
I almost always use ROW_NUMBER() in this situation. I have found that it just tends to flow better. But yes, using ROW_NUMBER() with a CTE or temp table is an exceedingly useful little trick when you want to get something like the descriptor value that is associated with a MIN/MAX value rather than the MIN/MAX value itself.
You all are awesome! This response far exceeded my expectations and I've learned a lot. I really appreciate each of you for taking the time to share your knowledge.
All the best,