Max function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, there is a batch program that runs everyday...each record has a field
batch_no which is populated everytime a batch is run. for example lets say
Day one is D1, day 2 is D2 etc. what statement would u use to grab the
records with the latest batch number? I am doing it in design view and i put
the max function on batch_no but it is giving me all the records prior to the
last batch run as well. any help? Thanks
 
nick said:
Hi, there is a batch program that runs everyday...each record has a field
batch_no which is populated everytime a batch is run. for example lets say
Day one is D1, day 2 is D2 etc. what statement would u use to grab the
records with the latest batch number? I am doing it in design view and i put
the max function on batch_no but it is giving me all the records prior to the
last batch run as well. any help? Thanks
Click to expand...

nick,

This should return all records that equal the maximum batch_no.

You can paste the following into SQL View (with a query open, menu:
Tools > SQL View).

SELECT YT1.batch_no
FROM YourTable AS YT1
WHERE YT1.batch_no =
(SELECT MAX(YT2.batch_no)
FROM YourTable AS YT2)

In the above SQL code, YourTable gets replaced with your table's name,
and you should select a more appropriate abbreviation for the table
aliases than YT1 and YT2 (something that more closely abbreviates your
table's name).


Sincerely,

Chris O.
 
Back
Top