I am working with a magento table like this:
我正在使用像这样的magento表：
+-----------+--------------+------------+--------------+----------+-------------+
| date | email | product_id | product_type | order_id | qty_ordered |
+-----------+--------------+------------+--------------+----------+-------------+
| 2017/2/15 | x@y.com | 18W1 | custom | 12 | 1 |
+-----------+--------------+------------+--------------+----------+-------------+
| 2017/2/15 | x@y.com | 18W2 | simple | 17 | 3 |
+-----------+--------------+------------+--------------+----------+-------------+
| 2017/2/20 | z@abc.com | 22Y34 | simple | 119 | 1 |
+-----------+--------------+------------+--------------+----------+-------------+
| 2017/2/20 | z@abc.com | 22Y35 | custom | 31 | 2 |
+-----------+--------------+------------+--------------+----------+-------------+
I want to make a new view by grouping by email, and then taking the row with the LEAST of order_id only.
我希望通过电子邮件分组来创建一个新视图，然后仅使用最少的order_id行。
So my final table after doing this operation from above should look like this:
所以从上面做这个操作后我的最终表应该是这样的：
+-----------+--------------+------------+--------------+----------+-------------+
| date | email | product_id | product_type | order_id | qty_ordered |
+-----------+--------------+------------+--------------+----------+-------------+
| 2017/2/15 | x@y.com | 18W1 | custom | 17 | 1 |
+-----------+--------------+------------+--------------+----------+-------------+
| 2017/2/15 | z@abc.com | 18W2 | simple | 31 | 3 |
+-----------+--------------+------------+--------------+----------+-------------+
I'm trying to use the following query (but it's not working):
我正在尝试使用以下查询（但它不起作用）：
SELECT * , (SELECT DISTINCT table.email, table.order_id,
LEAST (order_id) AS first_transaction_id
FROM
table
GROUP BY
email)
FROM table;
Would really love any help with this, thank you!
真的很喜欢这方面的任何帮助，谢谢！
1 个解决方案
#1
3
I think you want distinct on:
我想你想要明确：
select distinct on (email) t.*
from t
order by email, order_id;
distinct on is a Postgres extension. It takes one record for all combinations of keys in parentheses, based on the order by clause. In this case, it is one row per email, with the first one being the one with the smallest order_id (because of the order by). The keys in the select also need to be the first keys in the order by.
独特的是Postgres扩展。根据order by子句，括号中所有键组合需要一条记录。在这种情况下，每封电子邮件只有一行，第一行是order_id最小的一行（因为订单依据）。 select中的键也需要是order by中的第一个键。
#1
3
I think you want distinct on:
我想你想要明确：
select distinct on (email) t.*
from t
order by email, order_id;
distinct on is a Postgres extension. It takes one record for all combinations of keys in parentheses, based on the order by clause. In this case, it is one row per email, with the first one being the one with the smallest order_id (because of the order by). The keys in the select also need to be the first keys in the order by.
独特的是Postgres扩展。根据order by子句，括号中所有键组合需要一条记录。在这种情况下，每封电子邮件只有一行，第一行是order_id最小的一行（因为订单依据）。 select中的键也需要是order by中的第一个键。