Getting The Latest Record

Hello Everyone,

I would like advise on what I am doing. I am trying to get the maximum date per record from a table. It is a very simple query but I am wondering why I am not getting the right result. Please look at the following query:

select account_id, amount, max(tran_date) from evalucheck_history group by account_id, check_amount order by account_id

This query is supposed to give me the record with the latest date but instead I get the following (snap shot of the result):

Account ID Amt Date
9999999999000100 174.8 1-Dec-2006
9999999999000100 223.69 25-Oct-2006
9999999999000100 358.5 9-Nov-2006
9999999999000100 393.5 14-Nov-2006
9999999999000100 441.98 24-Oct-2006
9999999999000100 476.93 20-Oct-2006
9999999999000100 552.07 10-Jan-2007
9999999999000100 627.23 2-Nov-2006
9999999999000100 705.94 19-Oct-2006
9999999999000100 713.61 4-Dec-2006
9999999999000100 729.71 30-Oct-2006
9999999999000100 747.24 13-Mar-2007
9999999999000100 998.97 19-Apr-2007

Can you please help me on this?

Thank you all.

RandyI think the lack of a WHERE clause is why this isn't giving you what you want.
Here's a quick guess

SELECT account_id
,amount
,tran_date
FROM evalucheck_history
WHERE tran_date =
(
SELECT Max(x.tran_date)
FROM evalucheck_history As x
WHERE x.account_id = account_id
)
ORDER BY account_id|||um, george, what is that funky "x dot" notation supposed to be doing?

... FROM x.evalucheck_history As x|||SELECT account_id
, amount
, tran_date
FROM evalucheck_history as X
WHERE tran_date =
( SELECT Max(tran_date)
FROM evalucheck_history
WHERE account_id = X.account_id )
ORDER
BY account_id|||EDIT: Typo :)
Isn't yours the same as mine..?
(Except we aliased differently)|||test 'em and see :cool:|||Thank you for your help guys. I appreciate it. I had the problem sorted out.

Sincerely,

Randy|||Oooh my aliasing doesn't work!
It only returns a single record...

I'm not sure I get why either!|||Oooh my aliasing doesn't work!
It only returns a single record...

I'm not sure I get why either!maybe start a new thread? and show us your test data...|||No need for a new thread really?
I'm happy to bolt on here :p

SELECT x.*
FROM career As x
WHERE x.career_date =
(
SELECT Max(career_date)
FROM career
WHERE parent_identifier = x.parent_identifier
)

SELECT *
FROM career
WHERE career_date =
(
SELECT Max(x.career_date)
FROM career As x
WHERE parent_identifier = x.parent_identifier
)

(46223 row(s) affected)

Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row(s) affected)

Interesting, no?|||1 row, eh

:)|||I don't really get why though... I thought I had it a minute ago but other logic told me not to be stupid :p|||which row is it? my money is on the MAX(career_date) in the table ;)|||I thought that was obvious ;)
I just can't explain why!|||Your original alias was inside the subquery, which mean any testing in the subquery was NOT dependent on the outer query... thus

SELECT Max(x.tran_date)
FROM evalucheck_history As x
WHERE x.account_id = account_id

is the same as

SELECT Max(tran_date)
FROM evalucheck_history
WHERE account_id = account_id

As you can see account_id = account_id for every row. You may as well have left off the WHERE clause there because it'll give you the same result.

Thus, it returns one result only which is ALWAYS the max tran_date from the table, which will only every match one row in your outer query.

hope that helps ;)|||In summation your subquery should have been dependent, but it wasn't ...|||aschk, nice analysis

you should take up writing, you're good at it

:)|||Aha - now I see!
Thank you aschk and Rudy.

Oh and yes - very complete and concise answers - Rudy's suggestion is a good 'un!|||Thanks rudy, a very flattering comment. I probably wouldn't consider writing to be my best skill, but I do like analysing work and trying to better explain it (providing I understand it) ;)