June 18, 2008 at 6:26 am
I am selecting every contact at a particular list of firms - i.e. 1 firm may have 20, one 10, one 50 etc.
I have added some sub-queries to tell if the contact meets specific requirements - does the row have an email address etc.
From this I can sum the sub-queries and get a 'total' score for the contact.
Previously I have exported this data to Excel, sorted by Firm, then contact score (largest first), I can then assign a 'rank' to each contact at each firm - i.e. within Firm A contacts rank from 1-10, and for Firm B they rank from 1-4 etc - i.e. its a rank, but for each firm, not the entire data set.
I've read some stuff about rownumber, and it looks like I might be able to use that, but I still don't quite get it?
Is this something that can be achieved via rownumber?
June 18, 2008 at 6:31 am
My query is something like this:
SELECT T.*, [Total Coms]+[GP Contact]+[Has Email] as Contact_Score FROM
(SELECT
c.contact_id,
c.contact_title,
c.contact_surname,
f.firm_name,
--Contact Scoring--
(SELECT count(distinct communication_ID) from tblcommunication cn where cn.contact_id = c.contact_id) [Total Coms],
(SELECT count(contact_id) from tblcontact ct where ct.contact_id = c.contact_id and ct.contact_type like '%GP%') [GP Contact],
(SELECT count(contact_id) from tblcontact ct where ct.contact_id = c.contact_id and ct.contact_email like '%@%') [Has Email]
FROM tblcontact c
JOIN tblfirm f on f.firm_id = c.firm_id
JOIN tblfirm_type ft on ft.firm_type_id = f.firm_type_id
JOIN v_firm_address v on v.contact_id = c.contact_id
JOIN #tmpSuperUSGPs tmp on tmp.firm_id = f.firm_id
WHERE
f.firm_status = 1
AND
c.contact_status = 1)T
ORDER BY
Firm_name, contact_score desc
So, I would just want another column which basically assigned a rank from 1-x for each contact, starting over from 1 again when it reaches a new firm.
(Hopefully this makes it clearer!!)
June 18, 2008 at 6:39 am
There is a RANK function that works very similar to the ROW_NUMBER() function which would work better for your situation. check BOL for the RANK function. You would use the OVER clause with the RANK function which would partition the data according to the columns you need the ranking by, the partition clause is basically a GROUP BY clause.
June 18, 2008 at 8:14 am
Thanks Mark, I looked it up and it works a treat 🙂
RANK() OVER
(PARTITION BY firm_ID ORDER BY ContactScore DESC) AS 'RANK'
Did the trick just right!
June 18, 2008 at 8:31 am
Be sure to look at Dense_Rank as well as Rank. They are slightly different, but very similar. Might want to check it out, just in case.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 18, 2008 at 9:01 am
I've looked at rank and dense rank, I am not really sure about the no gaps thing - if a contact had a score of zero then would rank simply give a null and dense rank would still give it a rank?
Incidentally, looking online I see if two contacts have the same score they receive the same rank, as a result I have modified my original RANK to order by contact score desc, contact_surname, contact_firstname.
So, the only instance of duplicate ranks is a contact with identical score and identical name at the same firm, ok so there may be the odd John Smith, but on the whole it catches most things, I guess I could include the contact ID as the last ranking criteria - this is unique to each one, so there would never be a duplicate rank 🙂
June 18, 2008 at 9:17 am
They would both treat the score of 0 the same way.
The difference is simply that Rank will skip numbers after ties, while Dense_Rank won't.
For example, if you have 3 people with a score of 100, 2 with a score of 90 and 1 with a score of 80, Rank will give you:
Person1 1
Person2 1
Person3 1
Person4 4
Person5 4
Person6 6
Dense_Rank will give you:
Person1 1
Person2 1
Person3 1
Person4 2
Person5 2
Person6 3
The only difference is in how they handle ties. Dense_Rank would allow you to take your first and last name stuff out of it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply