We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Need help with MS SQL Server query

Medium Priority
349 Views
Last Modified: 2012-08-13
I have 2 tables I need to join them show the latest record/date as well as a count of records.  Wondering the best way to do it.  I started to write a simple count/group by but it just kept not grouping since the timestamps don't match and the count is always just 1.  here is what i currently have:

Select Top 9 CommentText, '/iPolicy/'+PolicyLink+'-'+CAST(HH_iPolicy_Data.iPolicyID as varchar)+'.htm' as link,CommentDate,COUNT(HH_iPolicy_Data.iPolicyID) as theCount
from HH_iPolicyComment_Data, HH_iPolicy_Data
Where HH_iPolicy_Data.iPolicyID = HH_iPolicyComment_Data.iPolicyID
Group By '/iPolicy/'+PolicyLink+'-'+CAST(HH_iPolicy_Data.iPolicyID as varchar)+'.htm',CommentDate,CommentText
Order By CommentDate Desc;

Open in new window


Tables [With colums of interest]:
HH_iPolicy_Data
*iPolicyID [PK]
*PolicyLink

HH_iPolicyComment_Data
*iPolicyCommentID [PK]
*iPolicyID [RelatedKey]
*CommentText
*ComentDate


The outcome I am looking for is to group the comments of the iPolicy showing the count (of total comments), the latest comment text and the datetime of the latest comment.

 
Comment
Watch Question

CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Select Top 9
    CommentText,
    '/iPolicy/'+PolicyLink+'-'+CAST(HH_iPolicy_Data.iPolicyID as varchar)+'.htm' as link,
    Max(CommentDate) as LatestCommentDate,
    COUNT(HH_iPolicy_Data.iPolicyID) as theCount,
    (select top 1 CommentText
      from HH_iPolicyComment_Data
      where iPolicyID = HH_iPolicy_Data.iPolicyID
      order by CommentDate desc) LatestCommentText
from
    HH_iPolicyComment_Data
join
    HH_iPolicy_Data
    on HH_iPolicy_Data.iPolicyID = HH_iPolicyComment_Data.iPolicyID
Group By PolicyLink, HH_iPolicy_Data.iPolicyID
Order By CommentDate Desc;
CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Correction, removed commenttext from the select list

Select Top 9
    '/iPolicy/'+PolicyLink+'-'+CAST(HH_iPolicy_Data.iPolicyID as varchar)+'.htm' as link,
    Max(CommentDate) as LatestCommentDate,
    COUNT(HH_iPolicy_Data.iPolicyID) as theCount,
    (select top 1 CommentText
      from HH_iPolicyComment_Data
      where iPolicyID = HH_iPolicy_Data.iPolicyID
      order by CommentDate desc) LatestCommentText
from
    HH_iPolicyComment_Data
join
    HH_iPolicy_Data
    on HH_iPolicy_Data.iPolicyID = HH_iPolicyComment_Data.iPolicyID
Group By PolicyLink, HH_iPolicy_Data.iPolicyID
Order By CommentDate Desc;

Author

Commented:
I get this error:

Msg 8127, Level 16, State 1, Line 15
Column "HH_iPolicyComment_Data.CommentDate" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:

try this
with CTE
as
(
	select iPolicyID, Max(CommentText) CommentText, Max(ComentDate) CommentDate, COUNT(*) TheCount
	from HH_iPolicyComment_Data
	group by iPolicyID, CommentText
)

Select Top 9 C.CommentText, 
       '/iPolicy/'+PolicyLink+'-'+CAST(C.iPolicyID as varchar)+'.htm' as link,
       C.CommentDate,
       C.TheCount
from HH_iPolicyComment_Data
inner join CTE C on C.iPolicyID = HH_iPolicyComment_Data.iPolicyID
Order By CommentDate Desc;

Open in new window

CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Thanks a million worked like a charm!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.