RE: Cool but ugly SQL

You're close, Eric - problem is that you have to put date and time into a 
single value, because max(time) is independent of the date - Don Fisher shows 
how, I think.

-------------- Original message -------------- 
From: "Eric Graeb" <egraeb@xxxxxxxxxxxxxxxxxxx> 

I apologize if I'm missing something here but I would have thought you could 
just use a grouping to accomplish this. You should be able to group by 
date, time having Max(date),max(time) right? 

-----Original Message----- 
From: midrange-l-bounces@xxxxxxxxxxxx 
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dan 
Sent: Thursday, June 15, 2006 1:59 PM 
To: Midrange Systems Technical Discussion 
Subject: Cool but ugly SQL 

I'm told this can be done. But I'm having a bugger of a time figuring this 
out. And it's starting to be crunch time. My file layout: 

SHH_LIB Char 10 
SHH_FILE Char 10 
SHH_MEMBER Char 10 
SHH_LCDATE Date 10 
SHH_LCTIME Time 8 
SHH_HASH Char 20 

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? 

TIA, 
Dan 
-- 
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.