如何获得每个客户端id的组id的最小日期?

I am trying to Select Group ID, and minimum dates per clients ID.

我正在尝试选择组ID，以及每个客户ID的最小日期。

This is sample data set.

这是样本数据集。

ClientID    GroupID DataDate        
1           9       2016-05-01      
2           8       2015-04-01
3           7       2016-07-05      
1           6       2015-01-05      
1           5       2014-11-12      
2           4       2016-11-02      
1           3       2013-02-14      
2           2       2011-04-01

I wrote

我写了

SELECT 
    clientID, MIN(DataDate)  
FROM sampleTable  
GROUP BY clientID

But in this query, I do not have GroupID selected. I need to include GroupID to join another table.

但是在这个查询中，我没有选择GroupID。我需要包含GroupID来加入另一个表。

If I do:

如果我做的事:

Select 
    ClientID, GroupID, MIN(DataDate)  
FROM sampleTable  
GROUP BY ClientID, GroupID

It won't really get minimum dates per client.

它不会为每个客户提供最少的日期。

Could you help me. How I should do this?

你能帮助我。我该怎么做呢?

2 个解决方案

#1

You can use ROW_NUMBER instead:

您可以使用ROW_NUMBER代替:

SELECT
    ClientID, GroupID, DataDate
FROM (
    SELECT *,
        rn = ROW_NUMBER() OVER(PARTITION BY ClientID ORDER BY DataDate)
    FROM SampleData
) t
WHERE rn = 1

If you want to include ties, use RANK instead of ROW_NUMBER.

如果要包含领带，请使用RANK而不是ROW_NUMBER。

#2

I hope i understood your question correctly . You want to display min dates for each client id's

我希望我理解对了你的问题。您希望显示每个客户id的最小日期

If my table has data like this:

如果我的表格有这样的数据:

    CID GID D1
    1   9   03-06-2016
    1   6   01-06-2017
    1   5   01-06-2015
    1   3   01-06-2014
    2   4   01-06-2017
    2   8   01-06-2014
    3   5   03-06-2016
    2   4   01-06-2011

Output :

输出:

    CID GID D1
    1   3   01-06-2014
    2   4   01-06-2011
    3   5   03-06-2016

This is what i think you can go with .

这就是我认为你能做的。

select cx.cid,cx.gid, cx.d1 from cli cx  where cx.d1=(select min(c1.d1) from cli c1 where c1.cid=cx.cid)
group by cx.cid,cx.gid,cx.d1
order by cx.gid

Hope it helps.

希望它可以帮助。

#1