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



Try this:
With TempFile as (Select shh_lib, shh_file, shh_member, max(char(shh_LCDate)
|| char(shh_LCTime)) as datetime from SRCMBRHASH group by shh_lib, shh_file,
shh_member) select * from SRCMBRHASH a join TempFile b on a.shh_lib =
b.shh_lib and a.shh_file = b.shh_file and a.shh_member = b.shh_member and
char(a.shh_LCDate || char(a.shh_LCTime) = b.datetime where etc...

Hope that helps. 

Donald R. Fisher, III
Project Manager
RoomStore, Inc.
(804) 784-7600 ext. 2124
dfisher@xxxxxxxxxxxxx


<clip>
The file key is the first five fields, which makes the key unique.
Duplicate values for the first three key fields are allowed and expected.
My task is to select the Lib/File/Member with the highest timestamp (LCDATE
& LCTIME), omitting any duplicate Lib/File/Member with earlier timestamps.
I'm also selecting certain library and file values.  Here is my current SQL:

  Select * from SRCMBRHASH where
        shh_Lib = 'PRODSRCE' and shh_File LIKE 'Q%'
    and shh_LCDate = (Select Max(shh_LCDate) from SRCMBRHASH
      where shh_Lib = 'PRODSRCE' and shh_File LIKE 'Q%' )
   Order by shh_Lib, shh_File, shh_Member, shh_LCDate desc,
             shh_LCTime desc

This "works", as in no syntax errors, but the result set is way too small.
I would expect every unique Lib/File/Member to appear (approximately 100),
but I'm only getting three rows.

Problem 1: How do I select the MAX of two fields, LCDate and LCTime?  (I
know, should be a real timestamp field, but that's a fix for the next
version.)
Problem 2: I think I need to compare shh_Lib to shh_Lib, shh_File to
shh_File, and shh_Member to shh_Member from the two selects in the
subselect.  But how?
<clip>

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

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.