Max and Max-1 in query

[Guest]

I have a table containing a equal number of records for several different
dates. I would like to write and query showing the most recent date (max) the
2 nd most recent date (max-1) and the variance between the two.

Can anyone help me with the logic I would use, preferably without entering
the dates in a form?

Thanks!

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use the TOP predicate in a query and a DESC sort on the date. When you
use the word "variance" do you mean the difference between the 2 values
or the square of the standard deviation (Var() function)?

SELECT TOP 2 <column list>, date_column, VAR(numeric_column) As Variance
FROM table
GROUP BY <column list>, date_column
ORDER BY date_column DESC

Change the table/column names to suit.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQksNC4echKqOuFEgEQJRRACgp1PG1ERIE4yH6OqGU5REze8Hil8AoKk1
xt/0fY9AJJs2rvC4uDzgoiT1
=HGd0
-----END PGP SIGNATURE-----