SQL query, distinct rows needed

Question

Hey guys, I have the following table structured like this:

So basically as you can see, the department goes through name changes every couple of years. Look at number 16 for example. I want a select query that will only get the name when the date is the greatest. How do I do that?

Answer 1

What is the primary key for this table? This does a subquery the same table with a name comparison.

SELECT
    id,
    name,
    date
FROM table
WHERE name = (SELECT TOP 1 name
              FROM table AS subtable
              WHERE subtable.name = table.name
              ORDER BY date DESC)

Answer 2

select ID, Name from departments o 
where o.thedate=
  (select max(i.thedate) from departments i where o.id=i.id)

Answer 3

SELECT ID, 
First(Name) AS FirstOfName, First(DateChange) AS FirstOfDateChange
FROM departments
GROUP BY ID
ORDER BY First(DateChange) DESC;

Answer 4

SELECT d.* FROM Departments d INNER JOIN (SELECT pk FROM Departments GROUP BY ID HAVING theDate=MAX(theDate)) m ON m.pk=d.pk WHERE [Name]="Department"