Get the CreatedAt Timestamp when the number of rows is equal to some number I have a simple table in SQL Server.




id
OtherId
createdAt




1
A
2019-11-28 05:03:03.983


2
A
2019-11-28 05:03:06.937


3
B
2019-11-28 05:03:12.983




I want to retrieve a timestamp when the number of rows becomes equal to or greater than a fixed number, let us say 10, where OtherId is A.
So, if the number of rows with A in the OtherId column is more than 10, then return the value from createdAt.
You could try along

SELECT
  id
  , createdAt
FROM (
  SELECT
    id
    , createdAt
    , RANK() OVER (PARTITION BY OtherId ORDER BY createdAt) rnk
  FROM Table1
) AS S
WHERE rnk = 8
;


The inner SELECT groups the records by OtherId, orders and ranks them by createdAt. The outer SELECT picks the record by some number.

The CTE (common table expression) @Akina mentioned, would pull the sub-select to the top:

WITH
S (id, createdAt, rnk) AS (
  SELECT
    id
    , createdAt
    , RANK() OVER (PARTITION BY OtherId ORDER BY createdAt) rnk
  FROM Table1
)
SELECT
  id
  , createdAt
FROM S
WHERE rnk = 8
;


Do not see much difference in this case. But the technique helps a lot for more complex settings as it allows to grow the statements and is usually more readable.

If, however, only a single OtherId is of interest, there is no need for the window function:

SELECT
    id
    , createdAt
FROM Table1
WHERE OtherId='A'
ORDER BY createdAt
OFFSET 7 ROWS
FETCH FIRST 1 ROW ONLY
;


On the other hand: Returning either a specific occurrence or the maximum could be done via a UNION:

WITH
S (otherId, createdAt, rnk, cnt) AS (
  SELECT
    OtherId
    , createdAt
    , RANK() OVER (PARTITION BY OtherId ORDER BY createdAt)
    , COUNT(createdAt) OVER (PARTITION BY OtherId)
  FROM Table1
)
SELECT
  otherId
  , createdAt
FROM S
WHERE rnk = 8
UNION ALL
SELECT
  otherId
  , MAX(createdAt)
FROM S
WHERE cnt &lt; 8
GROUP BY otherId
ORDER BY otherId
;


See it in action: db&lt;>fiddle

Please comment, if and as this requires adjustment / further detail.