Select most recent record in query

J

Janus9217

I have two tables, a personnel table and a qualification table. An
individuals qualifies multiple times per year so has multiple qualification
records from different dates.

How do I perform a query that only pulls up the most recent qualification
record for an individual?
 
D

Dodo

How do I perform a query that only pulls up the most recent
qualification record for an individual?
Click to expand...

Sort on one or the other and take top1?
(Visble in query design.)
 
P

peregenem

Dodo said:
Sort on one or the other and take top1?
Click to expand...

The aggregate MAX is the usual way e.g.

SELECT employee_ID, MAX(qualification_date)
FROM Skills
GROUP BY employee_ID;

TOP N is proprietary, horribly non-relational, gives incorrect results,
etc. MAX is in the SQL Standards.
 
P

peregenem

Dodo said:
It's a choice!
Click to expand...

Good moaning,
I can see that TOP N may be justifiable when N > 1 because you must
learn a set-based mindset to write the equivalent standard SQL code and
not everyone wants to take that road. Yes, TOP 1 is a choice but as an
alternative to MIN or MAX it may be a daft one :)
 
Top