Re: max(date) and grouping issue

Think you flipped your code around (comments is in the first select, but
not the group by and vice versa for the second)...

But it sounds like there are multiple records with different comments, thus
it's working exactly like you told it to.


If you only want one record, from which record should the comment be pulled?

Charles

On Tue, Oct 11, 2016 at 2:31 PM, Smith, Mike <Mike_Smith@xxxxxxxxxxxxxxxx>
wrote:

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.
--
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.