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

What I want to retrieve is a timestamp when the number of rows became a fixed number let us say 10 where OtherId is A

  • at the very minimum you need a way to decide which createdAt value to select. – Max Vernon Nov 29 at 20:30
  • COUNT() OVER (PARTITION BY OtherId ORDER BY createdAt) in CTE. – Akina Nov 29 at 20:34
  • @Akina I dont know much of CTE. but this would still give me a count. What I am looking for is Created date when rows for that id is like 10 or a fixed number – Mohit Nov 29 at 20:44
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.

  • This is great, thanks a ton @Abecee. I changed fiddle to understand it better dbfiddle.uk/… So, what about the performance If I have like a million rows for A, B and C. and I just want to get let say time stamp of 50th row, for that ID. Would that be bad for performance. Another thing is lets say I want to get the 8th Row. I am able to get it for 'A' since there are 8 rows. But for 'B' I want to get the max of Date in case the 8th row doesn't exist – Mohit Nov 29 at 21:48
  • @Mohit: The query is rather simple. With proper indexing, the performance should be at least decent. If you are restricting to just a single OtherId, you do not even need the window function - see above. To combine the value for some specific occurrence or the maximum, the CTE could help - see above as well. – Abecee Nov 29 at 22:44
  • This is great, thanks a ton @Abecee , – Mohit Nov 30 at 8:48

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.