Need SQL code for fields when using MAX function

[Robert]

When you use an aggregate function in a SELECT statement, you cannot specify
any fields you want they way you usually can in a SELECT statement. Only
fields that are part of the GROUP BY clause can be retrieved. How can I
create a join to select a record from a table using the MAX function and
then have use of all of the fields in that record? Anyone know?

This is something I have tried but it does not seem to work in Access:

SELECT t1.Column1, t1.Column2, t2.Column3, t2.Column4
FROM
(
SELECT Column1, MAX(Column2) AS Column2
FROM Table1
GROUP BY Column1

) t1
INNER JOIN Table1 t2
ON t1.Column1 = t2.Column1 AND t1.Column2 = t2.Column2
Thanks.

Robert

[MGFoster]

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

Perhaps this:

SELECT col1, col2, col3, col4
FROM table_name t1
WHERE col2 = (SELECT max(col2) FROM table_name
WHERE col1 = t1.col1)

Access JET SQL likes square brackets around tabular subqueries. E.g.:

SELECT ...
FROM [SELECT ... ]. As t1 INNER JOIN t2 ON t1.col = t2.col
WHERE ...

Note the period immediately succeeding the right square-bracket.

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

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

iQA/AwUBQdTV/4echKqOuFEgEQJ2ewCfbAVuDkQE3Q1qN+0WG7ioIEEND+0Anjp q
8K9mSHDBFnIjiFCMmB+dDYka
=Xbal
-----END PGP SIGNATURE-----
Robert wrote:

When you use an aggregate function in a SELECT statement, you cannot specify
any fields you want they way you usually can in a SELECT statement. Only
fields that are part of the GROUP BY clause can be retrieved. How can I
create a join to select a record from a table using the MAX function and
then have use of all of the fields in that record? Anyone know?

This is something I have tried but it does not seem to work in Access:

SELECT t1.Column1, t1.Column2, t2.Column3, t2.Column4
FROM
(
SELECT Column1, MAX(Column2) AS Column2
FROM Table1
GROUP BY Column1

) t1
INNER JOIN Table1 t2
ON t1.Column1 = t2.Column1 AND t1.Column2 = t2.Column2

[Robert]

I think this is working. You could also use SELECT TOP 1 ... ORDER BY col2
DESC, I think. Not sure which is better.

Thanks.

Robert

"MGFoster" <[e-mail]> wrote in message
news:gC***************@newsread3.news.pas.earthlin k.net...

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

Perhaps this:

SELECT col1, col2, col3, col4
FROM table_name t1
WHERE col2 = (SELECT max(col2) FROM table_name
WHERE col1 = t1.col1)

Access JET SQL likes square brackets around tabular subqueries. E.g.:

SELECT ...
FROM [SELECT ... ]. As t1 INNER JOIN t2 ON t1.col = t2.col
WHERE ...

Note the period immediately succeeding the right square-bracket.

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

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

iQA/AwUBQdTV/4echKqOuFEgEQJ2ewCfbAVuDkQE3Q1qN+0WG7ioIEEND+0Anjp q
8K9mSHDBFnIjiFCMmB+dDYka
=Xbal
-----END PGP SIGNATURE-----
Robert wrote:

When you use an aggregate function in a SELECT statement, you cannot
specify any fields you want they way you usually can in a SELECT
statement. Only fields that are part of the GROUP BY clause can be
retrieved. How can I create a join to select a record from a table using
the MAX function and then have use of all of the fields in that record?
Anyone know?

This is something I have tried but it does not seem to work in Access:

SELECT t1.Column1, t1.Column2, t2.Column3, t2.Column4
FROM
(
SELECT Column1, MAX(Column2) AS Column2
FROM Table1
GROUP BY Column1

) t1
INNER JOIN Table1 t2
ON t1.Column1 = t2.Column1 AND t1.Column2 = t2.Column2