SQL Statement error - cannot call methods on char. using rank() in subquery"

Hi,

We are trying to use a rank() and partition by statement in this SQL statement below in MS SQL but it seems to be failing on the group by line.

The Original Statement is:

SELECT DISTINCT 
Keyword_1.Level_One AS Keyword, 
Keyword_1.Level_One_Desc AS Keyword_Description,
Count(Incident.Incident_Number) AS Count  
FROM ((Incident  LEFT JOIN Keyword_1 ON Incident.Keyword_1=Keyword_1.Keyword left join period ON (Incident.Opened_Date >= period.start and  Incident.Opened_Date <= period.Salesend)) )
WHERE (Incident.Opened_Date >='21 Jan 2013' AND Incident.Opened_Date<='21 Jan 2013 23:59:59')  
GROUP BY Keyword_1.Level_One,Keyword_1.Level_One_Desc  
ORDER BY Count(Incident.Incident_Number) DESC

Select allOpen in new window

Below is the New Statement with Rank() and partition subquery to list only unique Incident_numbers  where RANK = 1. The subquery is returning the correct records but I then am unable to group those records as in the original statement.

SELECT DISTINCT
tmp.Keyword_1.Level_One AS Keyword,        
tmp.Keyword_1.Level_One_Desc AS Keyword_Description, 
Count(tmp.Incident_Number) AS Count 
from (
      select incident.*, keyword_1.*, Rank() over  (partition by incident.incident_number order by incident.roll ) as RANK
FROM ((Incident  LEFT JOIN Keyword_1 ON Incident.Keyword_1=Keyword_1.Keyword left join period ON (Incident.Opened_Date >= period.start and  Incident.Opened_Date <= period.Salesend))) 
      WHERE (Incident.Opened_Date >='21 Jan 2013' AND Incident.Opened_Date<='21 Jan 2013 23:59:59')
) tmp
where RANK = 1 
GROUP BY tmp.Keyword_1.Level_One,tmp.Keyword_1.Level_One_Desc
ORDER BY Count(tmp.Incident_Number) DESC

Select allOpen in new window

Running the above satement produces the error:

Msg 258, Level 15, State 1, Line 11
Cannot call methods on char.

The error message is complaining about the GROUP BY line, if I comment out the group by line, I get the same “Cannot call methods on char” error on the first line in the select, and then on the second line. The statement will only execute if I remove the first 2 components of the select and just leave the Count(tmp.Incident_number) which is just returning a count.

Can someone suggest a way to be able to still use the rank() and partition statement in a subquery and allow a group by as per the original statement.

Thanks