Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Selecting top date

    So far I have this code:
    Code:
    SELECT	   e.employee_number,
    	   MAX(a.appraisal_date) AS 'appraisal_date',
    	   a.appraisal_type
    
    FROM	   pwa_master.appraise a
    INNER JOIN pwa_master.people e
    	ON e.unique_identifier = a.parent_identifier
    
    GROUP BY   e.employee_number
    Which returns the below (example):
    The row I want to remove is highlighted
    00218 2006-02-06 00:00:00.000 V1
    00218 2002-10-31 00:00:00.000 V2
    00282 2006-02-06 00:00:00.000 V2
    00330 2004-10-31 00:00:00.000 V1

    Basically I only want to return:
    00218 2006-02-06 00:00:00.000 V1
    00282 2006-02-06 00:00:00.000 V2
    00330 2004-10-31 00:00:00.000 V1

    Is there anyway I can only pick out the top record based on appraisal_date?

    Cheers in advance

    - GeorgeV
    George
    Home | Blog

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that can only possibly ever work in mysql, because your GROUP BY is invalid, and mysql is the only database that will execute a query with an invalid GROUP BY

    just remove a.appraisal_type from the SELECT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    EDIT: should read GROUP BY e.employee_number, a.appraisal_type

    Must not have copied it right - sorry!

    Are you saying this cannot be done?
    I'm currently dabbling with subqueries...
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, now that you've fixed your GROUP BY, what was the question again?

    is this what you're trying to do?
    Code:
    SELECT e.employee_number
         , a.appraisal_date
         , a.appraisal_type
      FROM pwa_master.people as e
    INNER 
      JOIN pwa_master.appraise as a
        ON a.parent_identifier = e.unique_identifier
       AND a.appraisal_date =
           ( select max(appraisal_date)
               from pwa_master.appraise
              where parent_identifier = e.unique_identifier )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You won't believe this - but that's the answer I just came to!
    I was coming abck to post my answer, which is nearly identical to yours!
    (I still can't believe it!)

    Selecting top appraisal date record for each unique employee

    My solution:
    Code:
    SELECT	e.employee_number,
    	a.appraisal_date,
    	a.appraisal_type
    FROM	pwa_master.appraise a
    FULL JOIN pwa_master.people e
    	ON e.unique_identifier = a.parent_identifier
    WHERE 	a.appraisal_date IN 
    	(
    	SELECT MAX(appraisal_date)
    	FROM pwa_master.appraise
    	WHERE a.parent_identifier = parent_identifier
    	)
    AND employee_number IS NOT NULL
    ORDER BY employee_number
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why FULL JOIN? are you sure that's what you want?

    and why IN( )? you expect the subquery to return more than one max?????
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    There was a reason for it being FULL... FULL + the AND employee_number IS NOT NULL returned the right amount of expected results.

    I guess I don't need the IN - the subquery should only return the one result - you are correct (again)

    Cheers for your help!
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    There was a reason for it being FULL... FULL + the AND employee_number IS NOT NULL returned the right amount of expected results.
    you know what FULL means, right? it means get all people with and without matching appraisals, plus, get all appraisals with and without matching people

    assuming employee_number is not NULL itself, the only reason you need the qualification "AND employee_number IS NOT NULL" is because the FULL join returns appraisals without people, and you it turns out that don't want really those, after all

    in which case a simple LEFT or RIGHT is what you want, without that qualification

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

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yeah... there really was a reason I put a full join in - I didn't just chuck it in there, I understand what it pulls through.

    but yeah, I'm gunna pootle away now and stick in a RIGHT join, which should suffice.

    Thank you (I really mean this) for always pointing out my mistakes... It's the only way to learn!

    Now go play Nanaca Crash
    George
    Home | Blog

Posting Permissions

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