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