Filter table by value & Max value

NachitoMax 411 Reputation points
2022-03-24T17:58:26.147+00:00

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

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,919 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,564 questions
0 comments No comments
{count} votes

Accepted answer
  1. Naomi 7,361 Reputation points
    2022-03-24T18:02:01.02+00:00

    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


1 additional answer

Sort by: Most helpful
  1. NachitoMax 411 Reputation points
    2022-03-24T22:30:11.313+00:00

    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'