在SQL中,如何根据另一列中的最早日期选择一列中具有匹配值的行

时间:2021-01-06 19:23:32

I think this should be a simple SQL exercise, but I am not sure how it is done as I am new to querying dbs with SQL. I have a table that looks like this:

我认为这应该是一个简单的SQL练习,但我不确定它是如何完成的,因为我不熟悉使用SQL查询dbs。我有一个看起来像这样的表:

select * from myschema.mytable

customer_name                   date
         nick    2017-06-19 19:26:40
          tom    2017-06-21 19:24:40
        peter    2017-06-23 21:25:10
         nick    2017-06-24 13:43:39

I'd like for this query to return only one row for each unique name. Specifically, I'd like the query to return the rows for each customer_name with the earliest date. In this case, the first row for nick should be returned (with date 2017-06-19), but not the other row with date 2017-06-24.

我希望此查询只为每个唯一名称返回一行。具体来说,我希望查询返回每个customer_name的行与最早的日期。在这种情况下,应返回nick的第一行(日期为2017-06-19),但不返回日期为2017-06-24的另一行。

Is this a simple exercise in SQL?

这是SQL中的一个简单练习吗?

Thanks!

谢谢!

2 个解决方案

#1


3  

A simple MIN will do:

一个简单的MIN会做:

SELECT
    customer_name,
    MIN(date) AS earliest_date
FROM myschema.mytable
GROUP BY customer_name;

#2


0  

For this kind of problems you can use aggregate functions. what has to be done basically is grouping the rows by name and choosing the minimum date:

对于这类问题,您可以使用聚合函数。基本上要做的是按名称对行进行分组并选择最小日期:

select cutomer_name, MIN(date)
FROM myschema.mytable
GROUP BY customer_name

#1


3  

A simple MIN will do:

一个简单的MIN会做:

SELECT
    customer_name,
    MIN(date) AS earliest_date
FROM myschema.mytable
GROUP BY customer_name;

#2


0  

For this kind of problems you can use aggregate functions. what has to be done basically is grouping the rows by name and choosing the minimum date:

对于这类问题,您可以使用聚合函数。基本上要做的是按名称对行进行分组并选择最小日期:

select cutomer_name, MIN(date)
FROM myschema.mytable
GROUP BY customer_name