Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Sep 2003
    Location
    Kuwait
    Posts
    27

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

    Randy

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    Last edited by gvee; 07-11-07 at 12:05. Reason: Silly Mistake - see next post
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    um, george, what is that funky "x dot" notation supposed to be doing?

    ... FROM x.evalucheck_history As x
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    EDIT: Typo
    Isn't yours the same as mine..?
    (Except we aliased differently)
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    test 'em and see
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2003
    Location
    Kuwait
    Posts
    27

    Thumbs up

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

    Sincerely,

    Randy

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oooh my aliasing doesn't work!
    It only returns a single record...

    I'm not sure I get why either!
    George
    Home | Blog

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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?
    George
    Home | Blog

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1 row, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I don't really get why though... I thought I had it a minute ago but other logic told me not to be stupid
    George
    Home | Blog

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which row is it? my money is on the MAX(career_date) in the table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I thought that was obvious
    I just can't explain why!
    George
    Home | Blog

  15. #15
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •