Get earliest date with group by- SQL

Question

I need to get the earliest Created Date with group by identity number. Here the sample of record.

| AccountID | DisplayName | CreatedDate | IdentityNumber | OrganizationID | |-----------|---------------|-------------|----------------|----------------| | 1 | John | 1 Jan 2018 | 1234 | 1000 | | 2 | John | 15 Jan 2018 | 1234 | 1001 | | 3 | John | 20 Jan 2018 | 1234 | 1002 | | 4 | Michael | 1 Jan 2018 | 1235 | 1000 | | 5 | Michael | 3 Jan 2018 | 1235 | 1003 | | 6 | Wood | 2 Jan 2018 | 1236 | 1002 |

So I want to produce the result like this.

| AccountID | DisplayName | CreatedDate | IdentityNumber | OrganizationID | |-----------|---------------|-------------|----------------|----------------| | 1 | John | 1 Jan 2018 | 1234 | 1000 | | 4 | Michael | 1 Jan 2018 | 1235 | 1000 | | 6 | Wood | 2 Jan 2018 | 1236 | 1002 |

This my sql snippet

SELECT IdentityNumber, MIN(CreatedDate) FROM Accounts GROUP BY IdentityNumber

And yes I can get the earliest record with group by identity number. But how I can grab the AccountID, DisplayName, OrganizationID without group by?

John Cappelletti · Accepted Answer · 2018-01-24 16:43:39Z

5

Another option is using the WITH TIES clause in concert with Row_Number()

Select Top 1 with ties * From dbo.Accounts Order By Row_Number() over (Partition By IdentityNumber Order By CreatedDate)

answered Jan 24, 2018 at 16:43

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Cappelletti Over a year ago

@Chuki2 Yup. Tha'ts me in 4 words :)

user2983931 Over a year ago

what a much nicer way of doing it without a subselect.

Lamak · Accepted Answer · 2018-01-24 16:41:04Z

You can use ROW_NUMBER:

;WITH CTE AS ( SELECT *, RN = ROW_NUMBER() OVER( PARTITION BY IdentityNumber ORDER BY CreatedDate) FROM dbo.Accounts ) SELECT AccountID, DisplayName, CreatedDate, IdentityNumber, OrganizationID FROM CTE WHERE RN = 1;

Devmyselz · Accepted Answer · 2022-10-21 11:45:04Z

Use the MIN keyword

SELECT DISTINCT [AccountID],[DisplayName],MIN([CreatedDate]),[IdentityNumber],[OrganizationID] FROM dbo.[Accounts] GROUP BY [IdentityNumber]

the above code will return

AccountID	DisplayName	CreatedDate	IdentityNumber	OrganizationID
1	John	1 Jan 2018	1234	1000
4	Michael	1 Jan 2018	1235	1000
6	Wood	2 Jan 2018	1236	1002

Collectives™ on Stack Overflow

Get earliest date with group by- SQL

3 Answers 3

2 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Related