分组最大值

2021-11-20 00:00:00 sql mysql

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

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

表格:

我创建表的查询:SELECT id, security,buy_date FROM position 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 |
+-------+----------+------------+

我一直在处理基于 this 的查询版本页面，但我似乎无法得到我正在寻找的结果.

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.

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

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

相关文章