...you can use the ROW_NUMBER() OLAP function to create a sequence number for the duplicate rows based on some sort criteria and then select only the sequence 1 rows, like this:
with PRIORITISED_ROWS as (
select row_number() over(
partition by PREM
order by RDATE desc, RTIME desc
) as PRI,
X.*
from HSTREAD00 X
)
select *
from PRIORITISED_ROWS
where PRI = 1
This creates a PRI column that starts at 1 for each break of PREM and is sequence in descending date, time order, thus PRI = 1 is always the latest.
Tim.
________________________________
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Smith, Mike <Mike_Smith@xxxxxxxxxxxxxxxx>
Sent: 30 March 2020 16:50
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: [EXTERNAL] RE: sql troubles
Thanks for the reply
That doesn't seem to help much.
I still get both records for 20181212
SELECT prem, reading, rdate, max(digits(rdate)
concat(rtime)) FROM hstread00 WHERE rdate between
20181212 and 20190101 and batch <> 'OORTE' and prem =
'100' GROUP BY prem, reading, rdate
order by prem, rdate
PREM READING DATE MAX
100 14,936.00 20,181,212 20181212100000
100 14,939.00 20,181,212 20181212110020
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Justin Taylor
Sent: Monday, March 30, 2020 10:32 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] RE: sql troubles
Would this work:
select PREM, max( digits(RDATE) concat digits(RTIME) )
from hstread00
where rdate between 20181212 and 20190101 and batch <> 'ORTE' and prem = '100'
group by PREM
-----Original Message-----
From: Smith, Mike [mailto:Mike_Smith@xxxxxxxxxxxxxxxx]
Sent: Monday, March 30, 2020 9:13 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: sql troubles
I am trying to select data from a history file. There may be multiple records on a day, and I only want to select the record with the last time for the each date in the date range.
PREM Batch RDATE RTIME
100 MR07 20181212 100000
100 MR07 20181212 110020
100 MR07 20181213 100000
101 MR08 20181212 100000
The basics are
Select * from hstread00 where rdate between 20181212 and 20190101 and batch <> 'ORTE' and prem = '100'
I've can manage a single max date, or a single max time for the PREM, but I need the max time for each date for each PREM
Any help appreciated.
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@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Flinkprotect.cudasvc.com%2Furl%3Fa%3Dhttps%253a%252f%252flists.midrange.com%252fmailman%252flistinfo%252fmidrange-l%26c%3DE%2C1%2CwfUZTw4DL2K4gtrv28OXCEXbA2fe1xBRMlK04AnbZQLQf5kNMVLipVqWbpvaIz3BQSdN97WFQNpVmJtYxFd1nUL-BXzpa8vTnGpuo70Voic6wgOJMPpnARk%2C%26typo%3D1&data=02%7C01%7C%7C54af3486d2af4d25280d08d7d4b9b33c%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637211766348792951&sdata=CxIx43ZPgvx1x0LvUKkqY5S6unRBsllBIr5JGL8f09g%3D&reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Flinkprotect.cudasvc.com%2Furl%3Fa%3Dhttps%253a%252f%252farchive.midrange.com%252fmidrange-l.%26c%3DE%2C1%2CSWvhizQYVRh03X9MxOsNVEmGjYGZ996wYt5PMWzMi8KNpCYbJACqNgn9YhojLIfiXGiFoavQ7rv6qjrww9r_xUkLHe52ccJtv9Ci51w6wKYclCt7ieXb1Aw%2C%26typo%3D1&data=02%7C01%7C%7C54af3486d2af4d25280d08d7d4b9b33c%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637211766348792951&sdata=atdFZu%2B9dDvWXtgKGJE13Sxn%2FrFk1Gfpx7cAOG3DHhM%3D&reserved=0
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support
https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Flinkprotect.cudasvc.com%2Furl%3Fa%3Dhttps%253a%252f%252fmidrange.com%26c%3DE%2C1%2CWgALEgrARGdLtO5Cg8rmCIKepAR9_0q-JFCSV-nwpnE7xvSfDcKWtdpRf4STIfV7uuizn9rpR_wJ2IL3-9sZKKHPj0D0GYuC21ktg6h9OJFwUFEi%26typo%3D1&data=02%7C01%7C%7C54af3486d2af4d25280d08d7d4b9b33c%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637211766348792951&sdata=V06RPCVebsUn42GPCUmHhHV7DFztjkVQVqo5lIBxl98%3D&reserved=0 by shopping at amazon.com with our affiliate link:
https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Flinkprotect.cudasvc.com%2Furl%3Fa%3Dhttps%253a%252f%252famazon.midrange.com%26c%3DE%2C1%2CNus59IKUO894VnKMLibhMJMHT_rLEfEaEfcN6pNe_3dIENWF6mlLeqm4Su8UVVRP6LBno3tGbJU1gndoCSJ_Hwv0p44okoa7hj8eW7_InAA%2C%26typo%3D1&data=02%7C01%7C%7C54af3486d2af4d25280d08d7d4b9b33c%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637211766348792951&sdata=qDo347k8ofv3Wkd3kNblMuQwNWWp%2BglVii9phQ31uRs%3D&reserved=0
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@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=02%7C01%7C%7C54af3486d2af4d25280d08d7d4b9b33c%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637211766348792951&sdata=ALViwLySMrKJxHEqwLUwHSKQ3URxbLAisZl1G0HbE10%3D&reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=02%7C01%7C%7C54af3486d2af4d25280d08d7d4b9b33c%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637211766348792951&sdata=5O66GG4rwHnhEemigKJd%2Fh1gFp2JMxO54A4TuGs%2BHbw%3D&reserved=0.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com&data=02%7C01%7C%7C54af3486d2af4d25280d08d7d4b9b33c%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637211766348792951&sdata=ee29Xtn19rekrmyLW%2BpFVn0COP8nZt7klZEVBraM2CM%3D&reserved=0
midrange.com
