Yeah, once you have the outfile, you can use correlated table expressions - the "with xx as select..." kind of thing to essentially get a couple views over the data - one view could have the minima, the other the maxima - something like that.

I don't know the data well enough - just throwing out concepts. The CTEs can give you something like WHERE sometimestamp = (select min(jotimestamp) from such and such CTE)

I know this is sketchy - but it should all be doable - Charles already got you started, probably.


On 9/28/2012 1:54 PM, Stone, Joel wrote:
Thanks Vern

Your response sounds like I could select one cust change pair of journal records.

Can the first select get ALL the cust recs with the lowest time-stamp?

For example, cust A has 10 journal records and cust B has 2 journal records.

Can I build an SQL stmt that will return a min for cust A and also a min for cust B - in one SQL stmt?

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Friday, September 28, 2012 12:09 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL: how to farm journal: select only oldest and newest record for each cust #

Hi Joel

A quick thought - use a UNION SELECT. This assumes you have some
criteria for the particular set of records - the first SELECT would get
the record with some minimum timestamp, the second SELECT would get the
record with some maximum timestamp.

I'm just flying off the cuff with this - not sure it'd work at all -
depends on how the data comes out. It'll work in a general sense, though.


On 9/28/2012 10:32 AM, Stone, Joel wrote:
Is it possible (and simple) to select only two records from the journal - for each file-RRN combination?


Users make 20 changes throughout the day to CUST #123 record.

I want to select only the first "before" image and the last "after" image for that particular cust (and all other custs).

Can SQL select these with a simple statement?

Or is this best done in an HLL pgm?


This outbound email has been scanned for all viruses by the MessageLabs Skyscan service.
For more information please visit

This thread ...


Return to Archive home page | Return to MIDRANGE.COM home page