RE: max(date) and grouping issue -- MIDRANGE-L

Its in my select, so needs to be in my group by doesn't it.

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Darren Strong
Sent: Tuesday, October 11, 2016 2:27 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: max(date) and grouping issue

In your second query, the group by contains c.comments. This seems unnecessary and could be contributing to the additional records.

___________________________________
Darren Strong
Dekko

From: "Smith, Mike" <Mike_Smith@xxxxxxxxxxxxxxxx>
To: "Midrange Systems Technical Discussion
(midrange-l@xxxxxxxxxxxx)" <midrange-l@xxxxxxxxxxxx>
Date: 10/11/2016 02:19 PM
Subject: max(date) and grouping issue
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>

I am connecting to a sql server using jdbcr4 service program.

I have the following query

SELECT SMSCD, SMCLO, SMPRM, b.locationdescription, c.FacilityID, c.comments
max(c.completiondate)
FROM [Orders].[GISOwner].[SERVICEORDERS] a left outer join
[orders].[gisowner].[exposedgaspipe] b on smprm = premisenumber left outer join
[orders].[gisowner].[exposedpipeinspections] c on b.FacilityID = c.facilityid
where SMSCD = 'EMM' and SMCLO = ' '
group by SMSCD, SMCLO, SMPRM, b.locationdescription, c.facilityID

This seems to be working ok, but when I add an additional field(comments) to be selected, I start getting extra records from the C file.

SELECT SMSCD, SMCLO, SMPRM, b.locationdescription, c.FacilityID, max
(c.completiondate)
FROM [Orders].[GISOwner].[SERVICEORDERS] a left outer join
[orders].[gisowner].[exposedgaspipe] b on smprm = premisenumber left outer join
[orders].[gisowner].[exposedpipeinspections] c on b.FacilityID = c.facilityid
where SMSCD = 'EMM' and SMCLO = ' '
group by SMSCD, SMCLO, SMPRM, b.locationdescription, c.facilityID, c.comments

This is probably something silly, but I can't get it to work.

Mike
NOTICE: This message, including any attachment, is intended as a confidential and privileged communication. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender and delete this message.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

NOTICE: This message, including any attachment, is intended as a confidential and privileged communication. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender and delete this message.