Groupwise maximum

Question

I have a table from which I am trying to retrieve the latest position for each security:

The Table:

My query to create the table: SELECT id, security, buy_date FROM positions WHERE client_id = 4

+-------+----------+------------+
| id    | security | buy_date   |
+-------+----------+------------+
|    26 | PCS      | 2012-02-08 |
|    27 | PCS      | 2013-01-19 |
|    28 | RDN      | 2012-04-17 |
|    29 | RDN      | 2012-05-19 |
|    30 | RDN      | 2012-08-18 |
|    31 | RDN      | 2012-09-19 |
|    32 | HK       | 2012-09-25 |
|    33 | HK       | 2012-11-13 |
|    34 | HK       | 2013-01-19 |
|    35 | SGI      | 2013-01-17 |
|    36 | SGI      | 2013-02-16 |
| 18084 | KERX     | 2013-02-20 |
| 18249 | KERX     | 0000-00-00 |
+-------+----------+------------+

I have been messing with versions of queries based on this page, but I cannot seem to get the result I'm looking for.

Here is what I've been trying:

SELECT t1.id, t1.security, t1.buy_date 
FROM positions t1
WHERE buy_date = (SELECT MAX(t2.buy_date)
                    FROM positions t2
                    WHERE t1.security = t2.security)

But this just returns me:

+-------+----------+------------+
| id    | security | buy_date   |
+-------+----------+------------+
|    27 | PCS      | 2013-01-19 |
+-------+----------+------------+

I'm trying to get the maximum/latest buy date for each security, so the results would have one row for each security with the most recent buy date. Any help is greatly appreciated.

EDIT: The position's id must be returned with the max buy date.

Answer 1

You can use this query. You can achieve results in 75% less time. I checked with more data set. Sub-Queries takes more time.

SELECT p1.id, 
       p1.security, 
       p1.buy_date 
       FROM positions p1
left join
            positions p2
                on p1.security = p2.security
                   and p1.buy_date < p2.buy_date
      where 
      p2.id is null;

SQL-Fiddle link

Answer 2

You can use a subquery to get the result:

SELECT p1.id, 
  p1.security, 
  p1.buy_date 
FROM positions p1
inner join
(
  SELECT MAX(buy_date) MaxDate, security
  FROM positions 
  group by security
) p2
  on p1.buy_date = p2.MaxDate
  and p1.security = p2.security

See SQL Fiddle with Demo

Or you can use the following in with a WHERE clause:

SELECT t1.id, t1.security, t1.buy_date 
FROM positions t1
WHERE buy_date = (SELECT MAX(t2.buy_date)
                  FROM positions t2
                  WHERE t1.security = t2.security
                  group by t2.security)

See SQL Fiddle with Demo