Maximum Value

SQL 2008

I have a table with some example data as follows

Task   Audit    Status
1        22        Draft
1        23        Pending
1        24        Complete
2        25        Draft
2       26        Pending

I simple want to bring out the maximum audit value for each tasks

Results would be

Task Status
1       Complete
2       Pending

Any help would be appreciated
Mark WilsonBI DeveloperAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Should be simple as:
WITH TASK_CTE (Task, Audit)
AS
(SELECT Task, MAX(Audit)
FROM TableName
GROUP BY Task)
SELECT T.Task, T.Status
FROM Task T
INNER JOIN Task_CTE T2 ON T.Task = T2.Task AND T.Audit = T2.Audit

Open in new window

0
 
Scott PletcherSenior DBACommented:
SELECT Task, Status
FROM (
    SELECT Task, Status,
        ROW_NUMBER() OVER(PARTITION BY Task ORDER BY Audit DESC) AS row_num
    FROM a_table
) AS derived
WHERE
    row_num = 1
ORDER BY Task
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.