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