Filter table by value & Max value

Question

Hey

This doesnt seem to be working out for me, hopefully someone can offer an easy fix. Here is an example of my table data:

job_id      Item_id      Description      Rev_No
43566          3            Pipework       1
43566          4            Walling        1
43566          7            Bathroom       1
43566          4            Walling        2
43566          7            Bathroom       2
43566          7            Bathroom       3
43568          1            Floor          1

And i need to show the filtered result by the job_id and the rev_no like this

job_id      Item_id      Description      Rev_No
43566          3            Pipework       1
43566          4            Walling        2
43566          7            Bathroom       3

I am filtering the datacollection by job_id and only showing the max rev_no.

I set rev_no Group BY MAX, that didnt work
I also added this as a criteria in the WHERE clause but it just didnt work either.

WHERE (dbo.Table.job_id = 10) AND (dbo.table.rev_no = (SELECT MAX(dbo.table.rev_no) FROM dbo.table ))

I have jobs that have items and each item can have more than 1 revision. I need to show in a single list with all of the job items at the latest revision

Thanks

Answer 1

Try

; with cte as (select *, ROW_NUMBER() over (partition by job_id, item_id order by Rev_No DESC) as Rnk
from dbo.Jobs where job_id = 43566)

select * from cte where Rnk = 1 -- each item for the job in latest revision

Answer 2

Thanks for the reply.

I cant seem to find a way to implement that im afraid. I get 'SQL text cannot be presented in the grid pane and diagram pane'