Getting around the Last function 1

[cjkoontz]

Have noticed that the aggregate Last function is pretty useful.

But is there a way around it, using standard SQL?

With last, I can do something like this --

Select tblGame.BaseBallPlayer, Last(tblGame.Hits), Last(tblGame.AtBats)
From tblGame
Group By tblGame.BaseBallPlayer

 

[cjkoontz]

Sorry to answer my own question. But I think I found the answer on another site --

Select g1.BaseBallPlayer, g1.GameNbr, g1.Hits, g1.AtBats
From tblGame g1
join
(select BaseBallPlayer, max(GameNbr) GameNbr
from tblGame
group by BaseBallPlayer) g2
on g1.BaseBallPlayer = g2.BaseBallPlayer
and g1.GameNbr = g2.GameNbr
order by g1.BaseBallPlayer

 

[cjkoontz]

Sorry, again. I spoke too soon.

This will work with other SQL tools, but I can't seem to get it to work in Access.

 

[lespaul]

the structure in your second post should work in Access 2000 and above. What version are you running?

If you are running a lower version, then you have two choices. Create a query that is your JOIN query:

select BaseBallPlayer, max(GameNbr) GameNbr  
from tblGame
group by BaseBallPlayer

Save this query as qryMaxGames

then you can use the query in your join:

Select g1.BaseBallPlayer, g1.GameNbr, g1.Hits, g1.AtBats
From tblGame g1
join                                                       
qryMaxGames g2
on  g1.BaseBallPlayer = g2.BaseBallPlayer
and g1.GameNbr = g2.GameNbr
order by g1.BaseBallPlayer

Depending on the number of records, this solution will be quicker, but the one below will also work, it's a correlated subquery:

Select g1.BaseBallPlayer, g1.GameNbr, g1.Hits, g1.AtBats
From tblGame g1
WHERE g1.GameNbr = (SELECT Max(GameNbr) FROM tblGame g2 WHERE g1.BaseBallPlayer = g2.BaseBallPlayer)

HTH

Leslie

 

[cjkoontz]

Thanks, lespaul.

I used your first solution. I could sware that I did something similar and could not get it to work.

I did have to mod the qryMaxGames query, though, calling GameNbr as GameNbrMax. When I got both queries fixed up to run, I got a --

Circular reference caused by alias 'GameNbr'in query query definitions select list.

 

[lespaul]

it was missing the AS

max(GameNbr) GameNbr

glad to help and you got it working. thanks for the star!

les