CODEWITHSUNDEEP
sqlsql-server
user3220670
user3220670

Reputation: 79

How To Select Max DateTime of the Top 100 Rows Following A Given DateTime

I'd like to find the simplest way of handling the following psudo-sql:

SELECT MAX
(
  SELECT TOP 100 
    ModifiedDateTime 
  FROM tableA
  WHERE ModifiedDateTime > @StartDate
)

In short, I want to find EndDate of a given batch size given a StartDate.

One option here would obviously be to place the data into a #temp table or another intermediary table:

CREATE TABLE #LocalTempTable(
  LastModifiedDateTime DateTime)

INSERT INTO #LocalTempTable
  SELECT TOP(100)
    ModifiedDateTime
  FROM tableA
    WHERE ModifiedDateTime > @StartDate

And Extract the max from that table

SELECT MAX(ModifiedDateTime)
FROM #LocalTempTable

But I'd like a more elegant solution if one exists.

I've also tried to do it with taking the top row of a subquery but it results in a different value than the above #temp table solution:

SELECT TOP 1 * FROM
(
  SELECT TOP (100)
    ModifiedDateTime
  FROM tableA
    WHERE ModifiedDateTime > @StartDate
    ORDER BY ModifiedDateTime DESC
) AS EndDate

Upvotes: 0

Views: 703

Answers (2)

Sean Lange
Sean Lange

Reputation: 33581

Pretty sure you want something along these lines.

SELECT TOP 1 EndDate = ModifiedDateTime FROM
(
  SELECT TOP (100)
    ModifiedDateTime
  FROM tableA
    WHERE ModifiedDateTime > @StartDate
    ORDER BY ModifiedDateTime DESC
) AS x
order by x.ModifiedDateTime desc

--EDIT--

As scsimon pointed out this could simplified to this.

select top 1 ModifiedDateTime 
from FROM tableA 
WHERE ModifiedDateTime > @StartDate 
ORDER BY ModifiedDateTime DESC

Upvotes: 1

ASP
ASP

Reputation: 666

--Use Orderby on all test case that will give you correct answers

DECLARE @StartDate DATETIME = '2017-04-01'

; with cte_base (ModifiedDateTime)
AS
(select top 100 ModifiedDateTime
from TableA t
WHERE ModifiedDateTime > @StartDate
ORDER BY ModifiedDateTime DESC
)
SELECT *
FROM cte_base

Upvotes: 0

Related Questions