CODEWITHSUNDEEP
sqlsql-serverselectinner-join
Jeax
Jeax

Reputation: 57

where statement execute before inner join

I'm trying to grab the first instance of each result with a sysAddress of less than 4. However my statement currently grabs the min(actionTime) result first before applying the where sysAddress < 4. I'm trying to have the input for the inner join as the where sysAddress < 4 however i cant seem to figure out how to do it.

Should i be nesting it all differently? I didnt want to create an additional layer of table joins. Is this possible? I'm a bit lost at all the answers ive found.

    SELECT
  tblHistoryObject.info,
  tblHistory.actionTime,
  tblHistoryUser.userID,
  tblHistoryUser.firstName,
  tblHistoryUser.surname,
  tblHistory.eventID,
  tblHistoryObject.objectID,
  tblHistorySystem.sysAddress

FROM tblHistoryObject


JOIN tblHistory
  ON (tblHistory.historyObjectID = tblHistoryObject.historyObjectID)
JOIN tblHistorySystem
  ON (tblHistory.historySystemID = tblHistorySystem.historySystemID)
JOIN tblHistoryUser
  ON (tblHistory.historyUserID = tblHistoryUser.historyUserID)


INNER JOIN (SELECT
  MIN(actionTime) AS recent_date,
  historyObjectID
FROM tblHistory
GROUP BY historyObjectID) AS t2
  ON t2.historyObjectID = tblHistoryObject.historyObjectID
  AND tblHistory.actionTime = t2.recent_date


WHERE sysAddress < 4
ORDER BY actionTime ASC

Upvotes: 1

Views: 58

Answers (1)

MatBailie
MatBailie

Reputation: 86706

WITH
  all_action_times AS
(
    SELECT
      tblHistoryObject.info,
      tblHistory.actionTime,
      tblHistoryUser.userID,
      tblHistoryUser.firstName,
      tblHistoryUser.surname,
      tblHistory.eventID,
      tblHistoryObject.objectID,
      tblHistorySystem.sysAddress,
      ROW_NUMBER() OVER (PARTITION BY tblHistoryObject.historyObjectID
                             ORDER BY tblHistory.actionTime
                        )
                          AS historyObjectID_SeqByActionTime
    FROM
      tblHistoryObject
    INNER JOIN
      tblHistory
        ON tblHistory.historyObjectID = tblHistoryObject.historyObjectID
    INNER JOIN
      tblHistorySystem
        ON tblHistory.historySystemID = tblHistorySystem.historySystemID
    INNER JOIN
      tblHistoryUser
        ON tblHistory.historyUserID = tblHistoryUser.historyUserID
    WHERE
      tblHistorySystem.sysAddress < 4
)
SELECT
  *
FROM
  all_action_times
WHERE
  historyObjectID_SeqByActionTime = 1
ORDER BY
  actionTime ASC

This does exactly what your original query did, without trying to filter by action_time.

Then it appends a new column, using ROW_NUMBER() to generate sequences from 1 for each individual tblHistoryObject.historyObjectID. Then it takes only the rows where this sequence value is 1 (the first row per historyObjectID, when sorted in action_time order).

Upvotes: 1

Related Questions