How to get minimum date by group id per client id

Question

I am trying to Select Group ID, and minimum dates per clients 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.

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?

Answer 1

You can use ROW_NUMBER instead:

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.