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.

@Hogan Probably so. I haven't had a requirement like that for a while now, yours is of course far more correct. — Refugnic Eternium, Mar 04 '13 at 21:36
Please edit the question with all the details you've provided in the comments. That would make it a bit clearer. — Marian, Mar 04 '13 at 22:38

score 21 · Accepted Answer · edited Jul 25 '15 at 10:07

21

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

edited Jul 25 '15 at 10:07

potashin

44,205
11
83
107

answered Apr 25 '14 at 06:36

Vishal Zanzrukia

4,902
4
38
82

1

As far as I am concerned this is the best answer and although the previous one as marked as the answer this is more efficient. Good work. – Gerrit Brink Sep 18 '14 at 14:30
Thanks @Grep for your nice words :) Its always pleasure to hear such encourage. :) – Vishal Zanzrukia Sep 22 '14 at 07:29
1

@VishalZanzrukia Your answer is the best and I have updated the chosen response to yours given the same result with much greater efficiency. – Tomanow Nov 23 '16 at 19:31
How does this work? What I mean, by that, what's the logic behind it? – Nertan Lucian Jul 26 '23 at 22:08
Is it because the only row not matched is the maximum as there isn't a higher date so due to left join, it will be featured but with NULL on the other table's columns? – Nertan Lucian Jul 26 '23 at 22:16

score 9 · Answer 2 · answered Mar 04 '13 at 21:39

9

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

answered Mar 04 '13 at 21:39

Taryn

242,637
56
362
405

This seems very useful as well. Thanks for the input @bluefeet – Tomanow Mar 04 '13 at 21:48
1

@Tomanow The difference is that this will return the `id` that is associated with the `max(buy_date)` – Taryn Mar 04 '13 at 21:51
@bluefeet I am filtering by client_id = 4 that I did not mention since the table would return thousands of rows if I did not. When I add my filter to your queries, I still get only one entry which is the PCS row from my original post. – Tomanow Mar 04 '13 at 22:23
1

@Tomanow Please edit your post with the query that you are using and some sample data that matches. Your OP doesn't include `client_id` so I would be guessing at this point. :) – Taryn Mar 04 '13 at 22:24
1

I see what you mean about Hogan's answer, it does not output the correct id from the position with the max date. – Tomanow Mar 04 '13 at 22:36
we can achieve same result without using sub-query which is faster than this. – Vishal Zanzrukia Apr 25 '14 at 14:55

score 5 · Answer 3 · edited May 23 '17 at 12:34

5

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.

edited May 23 '17 at 12:34

Community

1
1

answered Mar 04 '13 at 21:31

Hogan

69,564
10
76
117

@Marian - that output was shown as being the wrong result, not the desired result. – Hogan Mar 04 '13 at 22:23
@Hogan, the result does not give me the correct id for the position with the max buy date. Here is the output, as you can see the dates do not match. – Tomanow Mar 04 '13 at 22:34
`+-------+----------+------------+--------------+ | id | security | buy_date | max_buy_date | +-------+----------+------------+--------------+ | 32 | HK | 2012-09-25 | 2013-01-19 | | 18084 | KERX | 2013-02-20 | 2013-02-20 | | 26 | PCS | 2012-02-08 | 2013-01-19 | | 28 | RDN | 2012-04-17 | 2012-09-19 | | 35 | SGI | 2013-01-17 | 2013-02-16 | +-------+----------+------------+--------------+` – Tomanow Mar 04 '13 at 22:34
@Tomanow - My solution does not return ID -- if you want ID use bluefeet's answer. His will be slower, but you will get the ID if you need it. – Hogan Mar 04 '13 at 22:36
1

@Marian - After a few years I've gotten used to it. :) – Hogan Mar 04 '13 at 22:37
My apologies, I edited the post for clarification. Thank you for the suggestion Hogan. – Tomanow Mar 04 '13 at 22:41
@Hogan: and now I can't change my vote unless you edit the answer. Oops. Care to add a small clarification? :) – Marian Mar 04 '13 at 22:59

ypercubeᵀᴹ · Answer 4 · 2013-03-05T00:06:37.377

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

score 1 · Answer 5 · answered Mar 04 '13 at 21:29

1

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.

answered Mar 04 '13 at 21:29

Colleen

23,899
12
45
75

2

Which table to fetch from? – hjpotter92 Mar 04 '13 at 21:32
No, because he wants the lastest position (not the lastest date), so the ID of the row with highest date – fred727 Nov 26 '21 at 15:41

Groupwise maximum

5 Answers5

Linked

Related