Re: [EXTERNAL] RE: sql troubles

...there is a comma missing before the Y but IMO the row_number() solution I posted before is the best general solution because you have complete flexibility over the way the duplicate rows should be ordered to find the one you want and, for me, the SQL is simpler than mangling together values, doing "max" on them and joining back.


________________________________
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Smith, Mike <Mike_Smith@xxxxxxxxxxxxxxxx>
Sent: 30 March 2020 17:23
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: [EXTERNAL] RE: sql troubles

Thanks Birgitta

I'm trying to test this out.
I get a Token Y was not valid.

I thought maybe it needed a 'With' before the Y , but no.
Then I thought maybe it needed a ',' before the Y, but then it wanted a group by.

Not sure what I might be missing.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Birgitta Hauser
Sent: Monday, March 30, 2020 11:14 AM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] RE: sql troubles

Sorry did not read the eMail correctly.

Here the modified SQL statement:
With x as (Select *
From YourTable
Where rdate between 20181212 and 20190101
and batch <> 'ORTE'
and prem = '100')
y as (Select PREM, Max(RDate concat RTIME) rtime
From x)
Select x.*
from x join y on x.Prem = y.Prem
and x.Rdate concat x.Rtime = y.rtime;

Mit freundlichen Grüßen / Best regards

Birgitta Hauser


"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to." (Richard Branson)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Birgitta Hauser
Sent: Montag, 30. März 2020 17:07
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: sql troubles

Try:
With x as (Select *
From YourTable
Where rdate between 20181212 and 20190101 and batch <> 'ORTE'
and prem = '100')
y as (Select PREM, RDATE, Max(RTIME) rtime
From x)
Select x.*
from x join y on x.Prem = y.Prem
and x.Rdate = y.Rdate
and x.RTime = y.RTime; Mit freundlichen Grüßen / Best regards

Birgitta Hauser


"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to." (Richard Branson)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Justin Taylor
Sent: Montag, 30. März 2020 16:32
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: 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://nam01.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%2CivF-zHylcNmL7rkj4v0HHyQ47oxFGepqxWwuPyDrT1o1p-e1qDw07KDR4bnBRV2qpVkH90Bx6UDDOSDBR9LMBgpel9ldWtuh_ZIIzNxpCiut3Jn8%26typo%3D1&amp;data=02%7C01%7C%7C6f4606285bd54ea9c4ae08d7d4be43cc%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637211785948073799&amp;sdata=L8l6Df96nQBiyEsglbe%2FIrZUXbI9Qn5BtzUc4k%2FAIss%3D&amp;reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://nam01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flinkprotect.cudasvc.com%2Furl%3Fa%3Dhttps%253a%252f%252farchive.midrange.com%252fmidrange-l.%26c%3DE%2C1%2CXxRBFVbegFjuOAam67QLIVh3JMjSBw5HFVqlTN7kKbzvZiEq4oKCS7z2R1MYg5d_m4O1R3j3vskmSPRMN3TMY5Nwattvi34APaA3F0YPtqVk3VbuinA%2C%26typo%3D1&amp;data=02%7C01%7C%7C6f4606285bd54ea9c4ae08d7d4be43cc%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637211785948073799&amp;sdata=aZRVYMNx0binZluw4COsTxf3UoEo3XKFe3cehrBxCho%3D&amp;reserved=0

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support https://nam01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flinkprotect.cudasvc.com%2Furl%3Fa%3Dhttps%253a%252f%252fmidrange.com%26c%3DE%2C1%2CPU3HiIfzlpjORvf8RtnhVVvphq37dGZ2FJ42gm2R3nJK-YOi4F__jgoh2j800yihIQkDFaQrPBnpODhIMSIdZ4byJhitjKzMiBWYIdDfp_HxxxArc_ldEj4kfsY%2C%26typo%3D1&amp;data=02%7C01%7C%7C6f4606285bd54ea9c4ae08d7d4be43cc%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637211785948073799&amp;sdata=VdRdgtxo6HappknHoHFsk6v0V6Mnc%2Fshp63mvAdQ1zM%3D&amp;reserved=0 by shopping at amazon.com with our affiliate link:
https://nam01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flinkprotect.cudasvc.com%2Furl%3Fa%3Dhttps%253a%252f%252famazon.midrange.com%26c%3DE%2C1%2CDKKGoe2CTw5NZjk-8wcVl7gK2IBZurU8sBVpc1dQYabWvVAUlC-5TAA6mfoEBr0NCr4LsBiBteQ0Q51aWonYH_yDhh_5dW5I2_ad0YGYJLit32tj4tWz%26typo%3D1&amp;data=02%7C01%7C%7C6f4606285bd54ea9c4ae08d7d4be43cc%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637211785948073799&amp;sdata=dlVa%2BZY0r%2BXubRFwkQG4O0l70lGl7A%2BV6gonpqVYWX0%3D&amp;reserved=0

--
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://nam01.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%2C7Y9zR8wEMyd9Yy0mzKgpcxIMYH_7zVHM7BQGQxVS5KR8omvQX3CvHQewi_AjR4XbEWMZDLN8LVABRuyk3euoGrkPbWYoShyvgp7_gw4U6Ny0E_pOTEw%2C%26typo%3D1&amp;data=02%7C01%7C%7C6f4606285bd54ea9c4ae08d7d4be43cc%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637211785948083786&amp;sdata=oyT18di6Bnb5OxKz76OZhoolGtHvTgVjtpcmbiG66Jo%3D&amp;reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://nam01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flinkprotect.cudasvc.com%2Furl%3Fa%3Dhttps%253a%252f%252farchive.midrange.com%252fmidrange-l.%26c%3DE%2C1%2C40_IGG5GLuINViqLx3eCg3DR743vwNrZ11ej8W_74DnFzYVjWNVnzoMumDIouMEnmyBFaaZyXJXCic39VvK8aIsQdNuYMf9mXLbj9_fVLTmTOg%2C%2C%26typo%3D1&amp;data=02%7C01%7C%7C6f4606285bd54ea9c4ae08d7d4be43cc%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637211785948083786&amp;sdata=UHB53V4CuY7Grg9B76xSYEzhmyiWnZr5Vdpqbvr9icI%3D&amp;reserved=0

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support https://nam01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flinkprotect.cudasvc.com%2Furl%3Fa%3Dhttps%253a%252f%252fmidrange.com%26c%3DE%2C1%2CvXCLyPUh2p2KIgLbcCXjKRwPzJeCHSfF8fHcgva-vV7LfejwV7Urxo8NE4JAxcgCOysBvDbJQw-Y8d8F26oRVfdMk9wK2SNbWD5F6rDNBi_7tF5VUeQf2BvdTs8%2C%26typo%3D1&amp;data=02%7C01%7C%7C6f4606285bd54ea9c4ae08d7d4be43cc%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637211785948083786&amp;sdata=emJQ8KYSywhaKO2mNjJViKdui3mnKdLLfpDgxdZPyoE%3D&amp;reserved=0 by shopping at amazon.com with our affiliate link:
https://nam01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flinkprotect.cudasvc.com%2Furl%3Fa%3Dhttps%253a%252f%252famazon.midrange.com%26c%3DE%2C1%2CUtQdpK0rmjegc5rVRszjrNMCOWPm1jLpwpIZlmeckDyPirC5GCa4E5908U0yMMEG0BsOGyYcQGU8MXLq4JlgYcBbmdicL8HeHLcqP5w8XrRjG_QB%26typo%3D1&amp;data=02%7C01%7C%7C6f4606285bd54ea9c4ae08d7d4be43cc%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637211785948083786&amp;sdata=A2tO5U%2B13GdntKhu8Nvz2Hl8OJUwXGj8onwuzhuiL7A%3D&amp;reserved=0

--
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://nam01.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%2CEWNocDpir3PcuRrKrb_rnbb932U4TAjETCHpTtaT_eFbCNN1KPQncjCek57M0MqE29dlOi0qgHL-nzqgzpYineIhQ4FaFnn-NvzTdnzr5iQR45pb%26typo%3D1&amp;data=02%7C01%7C%7C6f4606285bd54ea9c4ae08d7d4be43cc%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637211785948083786&amp;sdata=G08nekPI32nn6LMXW2fPkPekKolcNR13tMzXZQyk4Z8%3D&amp;reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://nam01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flinkprotect.cudasvc.com%2Furl%3Fa%3Dhttps%253a%252f%252farchive.midrange.com%252fmidrange-l.%26c%3DE%2C1%2CJ2-4w-SyRMsSdy21VRZHVj2ybkM7tGaUF-hf4yMgaYsfob8cZCq9pp-imGI6DZTNxFaYH6_DFYHx5CNRxWa1g3q3TMPH1mAw19E0OhHt1izicWfNcgkv3Hazy879%26typo%3D1&amp;data=02%7C01%7C%7C6f4606285bd54ea9c4ae08d7d4be43cc%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637211785948083786&amp;sdata=16rsBE%2BTa3b91yy5SvYo1Bn%2FejSQnkwZe9VYoH26bGw%3D&amp;reserved=0

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support https://nam01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flinkprotect.cudasvc.com%2Furl%3Fa%3Dhttps%253a%252f%252fmidrange.com%26c%3DE%2C1%2CflwETAqM0nDi4wQEkQJq7XnQcgWw0QYEbXO0HVmPCZzMbl0_VZYTPX-uXalaC2Vv0LqJg6BgizMUJ01dr1TbrJRZg0B_yWZNkQfBwBgVbV6-xVY%2C%26typo%3D1&amp;data=02%7C01%7C%7C6f4606285bd54ea9c4ae08d7d4be43cc%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637211785948093782&amp;sdata=yHH%2BMrx8%2BLBdt00qaIiFyqmn2fWYRTr6hQ4FXyoEnmE%3D&amp;reserved=0 by shopping at amazon.com with our affiliate link: https://nam01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flinkprotect.cudasvc.com%2Furl%3Fa%3Dhttps%253a%252f%252famazon.midrange.com%26c%3DE%2C1%2CffvIfMmhhDXnsZ7-bXA2ZHqFX68egUxjQucK84a2aQBCi7eMh8B59621v7DvYycYIxYBgZYSpC8egYzniQOW3jVjTBB4jBm470iW7Je3HuC1XZOW2CSzA1EJYQ%2C%2C%26typo%3D1&amp;data=02%7C01%7C%7C6f4606285bd54ea9c4ae08d7d4be43cc%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637211785948093782&amp;sdata=rk4wrWAatOl02ukfGcQdoMS5arUXcAQAc%2FlHDzSVImU%3D&amp;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://nam01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&amp;data=02%7C01%7C%7C6f4606285bd54ea9c4ae08d7d4be43cc%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637211785948093782&amp;sdata=p5xbx4zBQXsSMnZ2l%2Fp86BrrQhEUXgVGfq26wI0pvY4%3D&amp;reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://nam01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&amp;data=02%7C01%7C%7C6f4606285bd54ea9c4ae08d7d4be43cc%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637211785948093782&amp;sdata=xQBN13b0MONy4shNlk6PHPrFXr2Z%2F3TWBxTnwp9tlRY%3D&amp;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://nam01.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com&amp;data=02%7C01%7C%7C6f4606285bd54ea9c4ae08d7d4be43cc%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637211785948093782&amp;sdata=npfGDHi17Iv55SDLo%2BSFU1kCRubIyDAfVU1OBbrADZo%3D&amp;reserved=0