Select newest case, revisited

Results 1 to 2 of 2

Thread: Select newest case, revisited

  1. #1
    Join Date
    Dec 1969

    Default Select newest case, revisited<BR><BR>I dealt with this a while back with Bill and was quick to assume it was working correctly, only to find that in fact there was a slight discrepency, skewing the results. (You can visit the link above for the specifics.<BR><BR>The problem, is that in the above post, doing a MAX(CreateDate) causes it to select the max date in the table, vs the group. <BR><BR>The schema is as follows: (dates are actually long datetimes)<BR><BR>TestInstanceID &#124 testCaseID &#124 DateCreated <BR> 1 1 10/01/03<BR> 2 1 10/02/03<BR> 3 1 10/03/03<BR> 4 2 10/01/03<BR> 5 2 10/02/03<BR><BR>TestCase is like a template and instances are actual tests created FROM this template. <BR><BR>As these are run throughout the day, results are created and someone may or may not decide to schedule another COPY of that testcase, thus creating a new test instance, from the same parent TESTCASE.<BR><BR>All of these templates come from a parent called a DESIGN. For each DESIGN, I must grab some stats, specifically, how many of it&#039;s test cases passed, how many failed etc. <BR><BR>The constraint however, if multiple test instances share the same parent test case, I can only use the newest instance in the count.<BR><BR>Hope that&#039;s clear, thanks in advance.<BR>-Kevin

  2. #2
    Join Date
    Dec 1969

    Default Shouldn't be happening...

    Look at this part of the query:<BR><BR>SELECT testCaseID, Max(whenGenerated) AS when <BR> FROM TestInstances <BR> GROUP BY testCaseID<BR><BR>That *should* create a result set (which the main query then treats as a pseudo-table) which has the appropriate max date for *each* testCaseID.<BR><BR>What happens when you use *exactly* this query:<BR><BR>SELECT * <BR>FROM TestInstances AS TI,<BR> ( SELECT testCaseID, Max(DateCreated) AS when <BR> FROM TestInstances <BR> GROUP BY testCaseID ) AS latest <BR>WHERE TI.testCaseID = latest.testCaseID <BR> AND TI.DateCreated = latest.when <BR><BR>???<BR><BR>Show me what you get when you use that query, as is.<BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts