GROUP BY in Sub Query

Question

GROUP BY in Sub Query

alorenzini

SSCarpal Tunnel

Points: 4620
More actions
May 7, 2008 at 3:41 pm

#185192

I am trying to get the max effective date from the following query but the way I am doing I need to add a GROUP BY in the WHERE Clause but I keep getting a syntax error:
SELECTDISTINCT Listing.[ConsultantID]
,Listing.ConsultantName
,Listing.[ListType] AS OrderType
,Ord.ListType As ReportListType
,Ord.[TSUserType]
,Ord.[InternalUserName]
--,Ord.[ListTypeDesc]
,Ord.OrderTypeXID
,Ord.OrderStatusXID
,CONVERT(VARCHAR(10),Ord.ORderCreateDate,101) AS OrderCreateDate
,'G'+ CONVERT(VARCHAR(20),Ord.ORderGroupNumber) AS OrderGroupNumber
,Ord.OrderNumber
,ISNULL(Ord.PartOneTotal,0) As PartOneTotal
,ISNULL(Ord.PartTwoTotal,0) AS PartTwoTotal
,ISNULL(Ord.PartThreeTotal,0) as PartThreeTotal
,ISNULL(Ord.Hostcredit,0) AS HostCredit
,ISNULL(Ord.SampleAllowanceAmount,0) AS SampleAllowanceAmount
,ISNULL(Case
WHEN Listing.[ListType] IN ('Client')THEN ord.TSODiscountAmount
END,0)AS 'TSODiscounts'
,ISNULL(Case
WHEN Listing.[ListType] NOT IN ('Client')THEN ord.ConsultantDiscountAmount
END,0)AS 'NONTSODiscounts'
,ISNULL(Ord.ConsultantDiscountAmount,0) AS ConsultantDiscountAmount
,Ord.ShippedDate
,'ShipDate' =
CASE ord.OrderStatusXID
WHEN 32 THEN Convert(nvarchar(10),Ord.ShippedDate,101)
WHEN 16 THEN 'Partial'
WHEN 8 THEN 'Unshipped'
WHEN 128 THEN 'Unshipped'
END
,Ord.ShipToFirstName + ' ' +Ord.ShipToLastName AS ShipToName
,ISNULL(Ord.ShippingTotal,0) AS ShippingTotal
,ISNULL(Ord.OrderTotal,0) AS OrderTotal
,ISNULL(Ord.TaxTotal,0) AS TaxTotal
,Ord.SortOrder
FROM(SELECTDISTINCT con.[ConsultantID]
,Con.FirstName + ' ' + Con.LastName AS ConsultantName
,List.ListType
,MAX(con.EffectiveDate) AS EffectiveDate
FROM[Consultant]
GROUP BY Con.ConsultantID AS Con With (NoLock)
,(SELECT DISTINCT ListType FROM uvw_ListingType With (NoLock) WHERE (ListType = @OrderTypeFilter OR @OrderTypeFilter IS NULL AND ListType <> 'UFO')) AS List
WHERECon.ConsultantID = @ConsultantID) AS Listing
LEFT OUTER JOIN [uvw_OrderListingSummary] AS Ord ON
Listing.[ConsultantID] = Ord.[ConsultantID]
AND listing.listtype = ord.listtype
ANDOrd.[OrderCreateDate] BETWEEN @StartDate AND @EndDate
AND Ord.ListType IS NOT NULL
Order BY ord.SortOrder Asc
or maybe I am going about in the wrong way.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

holidasa SSC Eights! Points: 992 More actions · Answer 1

I have not tested whether the query will work but shouldn't the locking hint be moved to the FROM statement rather than the GROUP BY statement.

FROM WITH (NOLOCK)

GROUP BY

Also, there appears to be a missing close parenthesis after the GROUP BY statement. (I did not match all of the parens; just a quick glance.)

Sorry if this is not helpful.

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 2

Ah, I see where the problem might be:

(SELECT DISTINCT con.[ConsultantID]

,Con.FirstName + ' ' + Con.LastName AS ConsultantName

,List.ListType

,MAX(con.EffectiveDate) AS EffectiveDate

,(SELECT DISTINCT ListType FROM uvw_ListingType With (NoLock) WHERE (ListType = @OrderTypeFilter OR @OrderTypeFilter IS NULL AND ListType <> 'UFO')) AS List

FROM [Consultant] AS Con With (NoLock)

WHERE Con.ConsultantID = @ConsultantID

GROUP BY Con.ConsultantID ) AS Listing

This query won't run fast, but it should compile.

Correlated sub-queries will kill your performance. Also, depending on the clustered indexes, statistics, etc., instead of MAX with a GROUP BY, you might try getting TOP(1) with an ORDER BY. In some situations that can be faster. DISTINCT is a crutch that will destroy performance.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 3

I ended up doing a order by and reversing the direction.

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!