CODEWITHSUNDEEP
mysqlsql
Tomanow
Tomanow

Reputation: 7367

Groupwise maximum

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.

Upvotes: 9

Views: 8280

Answers (5)

Vishal Zanzrukia
Vishal Zanzrukia

Reputation: 4973

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

Upvotes: 21

Hogan
Hogan

Reputation: 70523

This is done with a simple group by. You want to group by the securities and get the max of buy_date. The SQL:

SELECT security, max(buy_date) 
from positions
group by security

Note, this is faster than bluefeet's answer but does not display the ID.

Upvotes: 5

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

The answer by @bluefeet has two more ways to get the results you want - and the first will probably be more efficient than your query.

What I don't understand is why you say that your query doesn't work. It seems pretty fine and returns the expected result. Tested at SQL-Fiddle

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 ) ;

If the problems appears when you add the client_id = 4 condition, then it's because you add it only in one WHERE clause while you have to add it in both:

SELECT t1.id, t1.security, t1.buy_date 
FROM positions t1
WHERE client_id = 4
  AND buy_date = ( SELECT MAX(t2.buy_date)
                   FROM positions t2
                   WHERE client_id = 4
                     AND t1.security = t2.security ) ;

Upvotes: 3

Taryn
Taryn

Reputation: 247690

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

Upvotes: 9

Colleen
Colleen

Reputation: 25489

select security, max(buy_date) group by security from positions;

is all you need to get max buy date for each security (when you say out loud what you want from a query and you include the phrase "for each x", you probably want a group by on x)

When you use a group by, all columns in your select must either be columns that have been grouped by or aggregates, so if, for example, you wanted to include id, you'd probably have to use a subquery similar to what you had before, since there doesn't seem to be any aggregate you can reasonably use on the ids, and another group by would give you too many rows.

Upvotes: 1

Related Questions