0

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.

0
2

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 < 8
GROUP BY otherId
ORDER BY otherId
;

See it in action: db<>fiddle

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.