Grouping results and filtering duplicates by the row's date I'm trying to write a query that gets all the records grouped by code and the rows that are duplicated take the one with the most recent date.
+----------+---------+-------------+
| code     | name    | date        |
+----------+---------+-------------+
| a        | Mike    | 2009-01-05  |
| b        | Jones   | 2010-02-06  |
| b        | Who     | 2009-01-05  |
+----------+---------+-------------+

expected result:
+----------+---------+-------------+
| code     | name    | date        |
+----------+---------+-------------+
| a        | Mike    | 2009-01-05  |
| b        | Jones   | 2010-02-06  |
+----------+---------+-------------+

I've gotten this far:
SELECT code, name, date
FROM table
GROUP BY code

I'm not sure how to aggregate a column based on another column's value.
Because you have other columns that you're not aggregating (name) but are eliminating rows based on the date column (latest record only), using GROUP BY is not ideal.
You can use windowing functions to find the specific record based on the grouping (code) and return only those records.
Example:
WITH CTE AS
(
  SELECT code
    , name
    , date
    , ROW_NUMBER() OVER (PARTITION BY code ORDER BY date DESC) AS [RowNumber]
  FROM Records
)

SELECT *
FROM CTE
WHERE RowNumber = 1

You can see a working example here. Basically it is using a CTE and the ROW_NUMBER function to assign a row number to each row within the code groups in a date descending order. This means you can simply select each row where the ROW_NUMBER value is 1.