Selecting top date

gvee · #1 (**permalink**) 02-13-07, 08:56

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

r937 · #2 (**permalink**) 02-13-07, 09:13

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

gvee · #3 (**permalink**) 02-13-07, 09:16

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

r937 · #4 (**permalink**) 02-13-07, 09:25

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 )

gvee · #5 (**permalink**) 02-13-07, 09:42

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

r937 · #6 (**permalink**) 02-13-07, 09:44

why FULL JOIN? are you sure that's what you want?

and why IN( )? you expect the subquery to return more than one max?????

gvee · #7 (**permalink**) 02-13-07, 10:48

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!

r937 · #8 (**permalink**) 02-13-07, 11:02

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

gvee · #9 (**permalink**) 02-14-07, 09:06

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

Internet Services

Web Development

Digital Marketing

Consumer Tech