Tough SQL statement 6

[levinll]

I'm using the below data and I'm trying to write some SQL code so for each uniqueHeader_ID in the table, I only get those records that have the latest date in the EditDate field. For example, for Header_ID #66963 I'd want to get back rows 6,7,8,9, and 10. For Header_ID #66964 I'd want to get back rows 11,12,13,14, and 15.

Can this be done ?

ID Header_ID EditDate
1 66963 10/28/2002 12:43:55 PM
2 66963 10/28/2002 12:43:55 PM
3 66963 10/28/2002 12:43:55 PM
4 66963 10/28/2002 12:43:55 PM
5 66963 10/28/2002 12:43:55 PM
6 66963 10/28/2002 12:47:04 PM
11 66964 10/28/2002 1:18:26 PM
13 66964 10/28/2002 1:18:26 PM
15 66964 10/28/2002 1:18:26 PM
7 66963 10/28/2002 12:47:04 PM
8 66963 10/28/2002 12:47:04 PM
9 66963 10/28/2002 12:47:04 PM
10 66963 10/28/2002 12:47:04 PM
12 66964 10/28/2002 1:18:26 PM
14 66964 10/28/2002 1:18:26 PM

 

[r937]

i can't read queries unless i format them nicely

select *  
  from GetEditedProcessedRecords AS XX 
 where XX.WeekEnding < @bdate 
   and XX.EditDate between @bdate 
                       and @edate 
   and (((
       (XX.EditDate)=
        ( select min(EditDate) 
            from GetEditedProcessedRecords 
           where Header_ID=XX.Header_ID )
       )))

the subquery gets the lowest EditDate overall for each Header_ID

however, the main query checks EditDate within a certain range

if the lowest EditDate overall is not in that range, you get no results

is that your problem?


rudy

 

[levinll]

I'm no SQL guru, so I can't say why the Min function doesn't work (I get no records back), and the Max function does.