We help IT Professionals succeed at work.

t-sql get minimum record from a date field

I'm using Sql Server 2019.

I have a table that looks like this.

Question1Table.PNG
This is the script to create the table.

USE [TestDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestTable2](
	[RecordID] [int] NOT NULL,
	[FromDate] [smalldatetime] NULL,
	[ToDate] [smalldatetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TestTable2] ([RecordID], [FromDate], [ToDate]) VALUES (1, CAST(N'2020-08-05T00:00:00' AS SmallDateTime), NULL)
GO
INSERT [dbo].[TestTable2] ([RecordID], [FromDate], [ToDate]) VALUES (2, CAST(N'2020-01-26T00:00:00' AS SmallDateTime), NULL)
GO
INSERT [dbo].[TestTable2] ([RecordID], [FromDate], [ToDate]) VALUES (3, CAST(N'2019-11-14T00:00:00' AS SmallDateTime), CAST(N'2020-01-19T00:00:00' AS SmallDateTime))
GO
INSERT [dbo].[TestTable2] ([RecordID], [FromDate], [ToDate]) VALUES (4, CAST(N'2019-08-19T00:00:00' AS SmallDateTime), CAST(N'2019-11-12T00:00:00' AS SmallDateTime))
GO

Open in new window



I only want the record with the earliest FromDate whose ToDate is NULL

So my desired result is this.

Question1DesiredResult.PNG

How do i revise this query to get that desired result? I was thinking of using MIN but not sure.

SELECT [RecordID]
      ,[FromDate]
      ,[ToDate]
FROM [TestDatabase].[dbo].[TestTable2]
Comment
Watch Question

Software Team Lead
try this:

Select RecordID, FromDate, ToDate
From
(
	Select *, ROW_NUMBER() over (order by FromDate, RecordID) idx from [TestTable2] where ToDate is null
) a
Where idx = 1
union all
select RecordID, FromDate, ToDate
from [TestTable2] where ToDate is not null

Open in new window

thanks. nice.