title: Using some sort of loop in a subquery of SQL
body: <p>I am making an SQL query in which I want to get some results for each latest entry of user, to do this I have made this query.</p>
  
  <pre><code>SELECT lA.attendance_id, lA.time_in, lA.time_out, lS.title, lS.end_time, lU.uid
  FROM logi_attendance AS lA
  INNER JOIN
      logi_users AS lU ON lA.user_id = lU.user_id
      INNER JOIN
          logi_shifts AS lS ON lU.shift_id = lS.shift_id
  WHERE
  (lA.time_in IS NOT NULL) AND
  (lA.time_out IS NULL) AND
  (lA.attendance_id =
      (
      SELECT TOP (1) A.attendance_id
      FROM logi_attendance AS A
      INNER JOIN
          logi_users AS B ON A.user_id = B.user_id
          ORDER BY A.attendance_id DESC
      )
  )
  </code></pre>
  
  <p>As you can see I have used a subquery to retrieve the latest records of each user (new records are created on daily basis) so getting the latest record is requirement. Now see that I have used TOP(1) in subquery which restrict it to return 1 record only. This allows the main query to run successfully but it is not useful because it only returns record of one user which it finds first. But I want to get results of all users, but it should be done in a way that subquery returns each latest id one by one so the main query executes successfully. Right now if I remove TOP(1) then it gives me an error that subquery returns more than 1 value which is incorrect.</p>
  
  <p>I don't know if I explained it properly, let me know if you don't understand what I am trying to ask and I will try to explain better.</p>
  
  <p>Thanks.</p>