分组最大值 [英] Groupwise maximum

问题描述

我有一张表格，我试图从中检索每种证券的最新头寸:

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

表格:

我创建表的查询: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.

这是我一直在尝试的:

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)

但这只是返回我:

+-------+----------+------------+
| 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.

必须返回购买日期和最大购买日期.

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

推荐答案

您可以使用此查询.您可以节省75％的时间.我检查了更多的数据集.子查询需要更多时间.

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 链接

SQL-Fiddle link

这篇关于分组最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！