sql server - "Simple" SQL Query

Question

Each of my clients can have many todo items and every todo item has a due date.

What would be the query for discovering the next undone todo item by due date for each file? In the event that a client has more than one todo, the one with the lowest id is the correct one.

Assuming the following minimal schema:

clients (id, name)

todos (id, client_id, description, timestamp_due, timestamp_completed)

Thank you.

Answer 1

This question is the classic pick-a-winner for each group. It gets posted about twice a day.

SELECT *
FROM todos t
WHERE t.timestamp_completed is null
  and
(
  SELECT top 1 t2.id
  FROM todos t2
  WHERE t.client_id = t2.client_id
    and t2.timestamp_completed is null
     --there is no earlier record
    and
    (t.timestamp_due > t2.timestamp_due
       or (t.timestamp_due = t2.timestamp_due and t.id > t2.id)
    )
) is null