× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Because TIME has a different value I don't think DISTINCT will help - unless you remove it from your query.

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Hoteltravelfundotcom
Sent: Wednesday, July 22, 2015 6:47 AM
To: Midrange Systems Technical Discussion
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.


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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.