April 22, 2011 at 1:44 pm
Hello,
I am using containstable and a few inner joins to build a ranking system on a search engine. I return data such as (title, url, information, homepage, rank) and I order by highest rank desc. What I want is distinct homepages it wouldn't be very useful if you return top 10 results and they are all from 'testsite.com...'. Also note I must have 10 results back so if top 10 results contain 4 duplicates I would need to scan more results until i get 10 uniquely different homepages. I might not be going in to as much detail as some may like but I am more than willing to explain further if needed. I won't show my exact ranking algorithms or code but I can show an example version of it if also needed. Thank You for your time.
April 22, 2011 at 4:40 pm
How about using
ROW_NUMBER() OVER(PARTITION BY homepage ORDER BY highest rank desc) as pos
and select the top 10 rows with pos=1 ORDER BY highest rank desc?
April 24, 2011 at 3:57 pm
Well... post title appears to have no relationship with actual issue - is that correct?
Usually the devil is in the details, in this case appears to be in the filtering to get rid of unwanted "testsite.com" style returned rows; distinct returned values shouldn't be a problem either.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply