CODEWITHSUNDEEP
sqlsql-server
mrtentje
mrtentje

Reputation: 1422

SQL max(DateTime) and category filter without group by

I've written the below query :

SELECT DateTime, configId, rowId
FROM linkedTableDefinition a,
INNER JOIN tableDefinition b,
ON a.Target = b.Id
INNER JOIN ViewWithInfo c,
ON a.Target = c.Id

This gives the following output:

DateTime           configId    rowId
12-09-2013 11:00     4           12
12-09-2013 12:00     4           12
12-09-2013 13:00     3           11
12-09-2013 12:00     3           11
12-09-2013 11:00     4           11

What I need of this output is the following: per rowId and configId combination I need the highest value from the datetime column. So from above example I want the following output:

DateTime           configId    rowId
12-09-2013 12:00     4           12
12-09-2013 13:00     3           11
12-09-2013 11:00     4           11

Does anyone know the answer? I would like to avoid GROUP BY because the select statement will be extended with a lot more columns.

Thanks in advance

EDIT The current query:

SELECT testResults.ResultDate, testResults.ConfigurationId, TestResultsTestCaseId
FROM dbo.FactWorkItemLinkHistory workItemLink
INNER JOIN dbo.DimWorkItem workItem 
ON workItem.System_Id = workItemLink.TargetWorkItemID
INNER JOIN dbo.TestResultView testResults
ON testResults.TestCaseId = workItemLink.TargetWorkItemID
WHERE 
RemovedDate = convert(datetime, '9999-01-01 00:00:00.000')
AND workItemLink.SourceWorkItemID = 7
AND workItem.System_WorkItemType = 'Test Case'

Upvotes: 0

Views: 592

Answers (3)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

SELECT *
FROM
(
    SELECT DateTime, configId, rowId, 
        ROW_NUMBER() OVER(PARTITION BY configId, rowId ORDER BY DateTime DESC) AS RowNum
    FROM linkedTableDefinition a
    INNER JOIN tableDefinition b ON a.Target = b.Id
    INNER JOIN ViewWithInfo c ON a.Target = c.Id
) src
WHERE src.RowNum = 1

Upvotes: 3

Bill Hall
Bill Hall

Reputation: 586

You could do this, substituting proper joining fields as appropriate:

SELECT testResults.ResultDate, testResults.ConfigurationId, testResults.TestCaseId,
    (SELECT MAX(ResultDate) FROM dbo.TestResultView WHERE TestCaseId = testResults.TestCaseId AND ConfigurationId = testResults.ConfigurationId) AS MaxDate
FROM dbo.FactWorkItemLinkHistory workItemLink
INNER JOIN dbo.DimWorkItem workItem 
ON workItem.System_Id = workItemLink.TargetWorkItemID AND    workItemLink.TeamProjectCollectionSK = workItem.TeamProjectCollectionSK
INNER JOIN dbo.TestResultView testResults
ON testResults.TestCaseId = workItemLink.TargetWorkItemID
WHERE 
RemovedDate = convert(datetime, '9999-01-01 00:00:00.000')
AND workItemLink.SourceWorkItemID = 7
AND workItem.System_WorkItemType = 'Test Case'
AND workItem.System_RevisedDate = convert(datetime, '9999-01-01 00:00:00.000')

Upvotes: 0

M.Ali
M.Ali

Reputation: 69524

Dont know much about the columns in your table but it would be something like this.... Query in CROSS APPLY with get the highest TOP 1 date for every result and then feed into the result set or outer query.. 

 SELECT configId, rowId, tbl.DateTime
    FROM linkedTableDefinition a,
    INNER JOIN tableDefinition b,
    ON a.Target = b.Id
    INNER JOIN ViewWithInfo c,
    ON a.Target = c.Id
                       CROSS APPLY
                                  (
                                   SELECT TOP 1 DateTime
                                   FROM whatevertable
                                   WHERE whatevertable.CommonCol = TabeFrmOuterQuery.CommonCol
                                  ORDER BY DateTime DESC
                                    ) tbl

Upvotes: 0

Related Questions