Getting The Latest Record

tangcov · #1 (**permalink**) 07-11-07, 10:20

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.

Randy

gvee · #2 (**permalink**) 07-11-07, 10:32

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

Code:

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

r937 · #3 (**permalink**) 07-11-07, 11:01

um, george, what is that funky "x dot" notation supposed to be doing?

... FROM x.evalucheck_history As x

r937 · #4 (**permalink**) 07-11-07, 11:03

Code:

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

gvee · #5 (**permalink**) 07-11-07, 11:04

EDIT: Typo

Isn't yours the same as mine..?
(Except we aliased differently)

r937 · #6 (**permalink**) 07-11-07, 11:21

test 'em and see

tangcov · #7 (**permalink**) 07-11-07, 15:14

Thank you for your help guys. I appreciate it. I had the problem sorted out.

Sincerely,

Randy

gvee · #8 (**permalink**) 07-12-07, 03:21

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

I'm not sure I get why either!

r937 · #9 (**permalink**) 07-12-07, 06:22

Quote:

Originally Posted by georgev

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...

gvee · #10 (**permalink**) 07-12-07, 09:05

No need for a new thread really?
I'm happy to bolt on here

Code:

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
	)

Code:

(46223 row(s) affected)

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

(1 row(s) affected)

Interesting, no?

r937 · #11 (**permalink**) 07-12-07, 09:21

1 row, eh

gvee · #12 (**permalink**) 07-12-07, 09:23

I don't really get why though... I thought I had it a minute ago but other logic told me not to be stupid

r937 · #13 (**permalink**) 07-12-07, 09:28

which row is it? my money is on the MAX(career_date) in the table

gvee · #14 (**permalink**) 07-12-07, 09:30

I thought that was obvious

I just can't explain why!

aschk · #15 (**permalink**) 07-12-07, 10:42

Your original alias was inside the subquery, which mean any testing in the subquery was NOT dependent on the outer query... thus

Code:

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

is the same as

Code:

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

Internet Services

Web Development

Digital Marketing

Consumer Tech