[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Need help with MS SQL Server query

Posted on 2011-04-18
6
Medium Priority
?
340 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.

 
0
Comment
Question by:RDurish
  • 3
  • 2
6 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 35420107
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;
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 35420113
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;
0
 

Author Comment

by:RDurish
ID: 35420147
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.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35420168

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

0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 2000 total points
ID: 35420647
Just a problem with the ORDER BY

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 LatestCommentDate Desc;
0
 

Author Closing Comment

by:RDurish
ID: 35421178
Thanks a million worked like a charm!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question