Can I sort before applying the LAST function? 1

[josephwalter]

A record somehow got entered out-of-order. Now the Last function in one of my aggregate queries is returning the incorrect row. Here are the details:

Table Name = tblData

Date Period Week Balance
07/26/03 7 4 150
07/20/03 7 4 100
07/21/03 7 4 200
07/22/03 7 4 300
07/23/03 7 4 400
07/24/03 7 4 350
07/25/03 7 4 250
07/27/03 8 1 50

My Query:
Select First(Balance) as BeginningBalance, Period, Week
From tblData
Group by Period, Week

The above SQL returns 150 for Period 7's beginning balance. I want it to return the 100 balance from the 7/20 record.

I've already tried changed the From clause to 'From (Select * from tblData Order by Date Asc)'. Plus, I've tried sorting the data in the table. No luck...

Any other ideas?

 

[josephwalter]

Sorry, the title of this thread should be "Can I sort before applying the FIRST function?"

 

[swampBoogie]

select period,week,balance
  from tblData as q
 where [date] = (select min([date])
 from tblData 
where period = q.period
  and week = q.week)

 

[josephwalter]

That worked great! Now can I throw another spin on this? The Balance field is actually an OpeningBalance. I have another field in the table called ClosingBalance. How can I query the OpeningBalance of the period's first day and the ClosingBalance of the period's last day?

 

[swampBoogie]

select period,week,openingbalance,
(select closingbalance from tblData q2
  where week = q.week
    and period = q.period
    and [date] = (select max([date])
   from tblData
   where week = q2.week
     and period = q2.period) as closed
  from tblData as q
 where [date] = (select min([date])
 from tblData
where period = q.period
  and week = q.week)