Re: Use of MAX? -- MIDRANGE-L

I cheat:

select * from MYFILE where rrn(MYFILE) in
(select max(rrn(MYFILE)) from MYFILE group by FIELD1, FIELD2, FIELD3)

I know, it's cheesy. But it works. In this case, gives you the last one in the set. Min(rrn) gives you the first. Of course, rrn isn't reliable as an ordering tool especially on a file with REUSEDLT, you never know which one you'll get. But as long as you don't care WHICH record in the set you get, this works for any file, for any fields.

Joe

Neither Distinct nor Max (with Group by) will work for you in this case Distinct will return every distinct row (even when just date or time is different), and Max will return values from potentially multiple rows since it returns the maximum value in the column within the group. I just answered a very similar question on StackOverflow. You can use a CTE and a TABLE() function to achieve what you are looking for.

Here is the SQL I posted on StackOverflow:
WITH head (otord#, ojhttn$) as (
SELECT DISTINCT otord#, ojhttn$
FROM replib.clspaytpl
WHERE otusrn in ('ANDON','SCH','JRU','BERT','TRA','LINA')
AND ottrnd >= 20140701)
SELECT h.otord#, h.ojhttn$, d.otusrn, d.ottrnc, d.ottrnd
FROM head h,
TABLE (SELECT *
FROM replib.clspaytpl
WHERE otord# = h.otord#
AND ojhttn$ = h.ojhttn$
AND otusrn in ('ANDON','SCH','JRU','BERT','TRA','LINA)
AND ottrnd >= 20140701
FETCH FIRST ROW ONLY) d

Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx

-----Hoteltravelfundotcom <hoteltravelfun@xxxxxxxxx> wrote: -----
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
From: Hoteltravelfundotcom <hoteltravelfun@xxxxxxxxx>
Date: 07/22/2015 08:47AM
Subject: Re: Use of MAX?

Briggitta, I have the same sql as previous all I am doing is adding the MAX
to the 5 columns. I want to avoid displaying what is a duplicate row. When
the rep entered the same order#, code, etc we only want to show 1. SO I
though MAX would give me only one in this case. Or do you use DISTINCT?
you have

orde rep CODE DATE TIME
123 BOB AAA 072215 090000
123 BOB AAA 072215 093600

I only want one of these and the date time will never be the same the other
3 cols if the same i just need 1 row.

On Wed, Jul 22, 2015 at 8:36 AM, Jim Oberholtzer <
midrangel@xxxxxxxxxxxxxxxxx> wrote:

Carel,

I understand your point but that's not the goal of a list like this. Many
times we answer the same question, asked a different way than the last
time.
All folks benefit, and most folks on this list simply read the posts and
rarely if ever post themselves, even folks that have not run across this
SQL
function yet will be helped. I ask SQL questions and yes I could take a
class, read a book etc., but my knowledge is good enough for my needs most
of the time, so I was helped by this answer even though I did not pose it.

If you don't like a question or list member, ignore them but never berate
them for asking a question.

--
Jim Oberholtzer
Chief Technical Architect
Agile Technology Architects

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Carel
Teijgeler
Sent: Wednesday, July 22, 2015 4:56 AM
To: Midrange Systems Technical Discussion
Subject: Re: Use of MAX?

Without sounding offending, but this is a simple RTFM.

This has been advised many times to you, but for every simple SQL question
you come to this list, while a simple search on the Internet or the
appropriate manual would have helped you.

Have you ever considered to take a course in SQL or read a book/article on
the basic principles on SQL? I advice you to do it. It saves you a lot of
time.

Regards,
Carel Teijgeler

On 22-7-2015 0:15, Hoteltravelfundotcom wrote:

HI I want to use MAX in this SQL. Because we have some cases where
Duplicates are entered. IN this particular sales data, there is no
check in the application.

So I have a reporting tool using a view and would need to remove these
dupes. The dupes will be manifest in these 5 fields and the time is
the only one that will differ (possibly the date) so If I select on
MAX I would get a distinct: So far it is not allowing, it gives an
error:

"SQL0122 - Column OHPTTC or expression in SELECT list not valid."
The dupes appear here: an example: We want only the 001 row but only
1 of the next 2
---------------------------------
OTORD# OTUSRN OTTRNC OTTRND OTTRT

05648230 MMOLINA 001 20,150,610 105,428
05648230 MMOLINA RRF 20,150,610 113,127
05648230 MMOLINA RRF 20,150,610 105,443

SELECT max(T01.OTORD#), max(T01.OTUSRN),max(T01.OTTRNC),
max(T01.OTTRND),
max(T01.OTTRT), T01.OHPTTC, T01.OHSLR#, T01.OHORDT,
T01.OHORDD, T01.OHTTN$, T02.RFCAT, T02.RFSLC, T02.RFSQ2, T02.RFDTA
FROM SLSDTA.CLSPAYT22 T01 INNER JOIN REPDTA.REFERRF T02 ON
T01.OTTRNC = T02.RFSLC WHERE RFCAT = '5058' AND RFSQ2 = '1'

GROUP BY T01.OTORD#, T01.OTUSRN, T01.OTTRNC, T01.OTTRND, T01.OTTRT

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

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