Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Filter table on highest revision number

Author  Topic 

ArnoldG
Starting Member

36 Posts

Posted - 2014-08-19 : 10:27:19
Hello,
I have a Select query like this:

SELECT
[QuoteNumber]
,[QuoteRevNo]
,[QuoteType]

FROM
Table


The result set is this:

QuoteNumber QuoteRevNo QuoteType
2015003 1 Project
2015003 2 Project
2015003 3 Project
2015004 1 Project
2015006 0 NULL
2015010 0 NULL
2015011 1
2015011 2


Now I would like to filter this table on only the highest revision numbers.
So only 2015003 rev 3 should be shown and not 2015003 rev 1 and 2

How would I do this?

Thanks in advance for helping me out...

Muj9
Yak Posting Veteran

75 Posts

Posted - 2014-08-19 : 11:31:10
create table test200 (
[QuoteNumber] varchar(255),
[QuoteRevNo] int
,[QuoteType] varchar(255))

insert into test200
select '2015003' as QuoteNumber,1 as QuoteRevNo,'Project' as QuoteType
union all
select '2015003' as QuoteNumber,2 as QuoteRevNo,'Project' as QuoteType
union all
select '2015003' as QuoteNumber,3 as QuoteRevNo,'Project' as QuoteType
union all
select '2015004' as QuoteNumber,1 as QuoteRevNo,'Project' as QuoteType
union all
select '2015006' as QuoteNumber,0 as QuoteRevNo,null as QuoteType
union all
select '2015010' as QuoteNumber,0 as QuoteRevNo,null as QuoteType

select
[QuoteNumber]
,max([QuoteRevNo]) as Max_QuoteRevNo
,[QuoteType]
from test200
group by
[QuoteNumber]
,[QuoteType]
Go to Top of Page

ArnoldG
Starting Member

36 Posts

Posted - 2014-08-19 : 13:56:17
Works fine on the example.
Just what I need. Thanks for that.

However I have some more columns that I had left out for the clarity of this example (shouldn't have done that).
If I add them in the SELECT and GROUP BY section, the list expands. How should I deal with that?

Go to Top of Page

ArnoldG
Starting Member

36 Posts

Posted - 2014-08-19 : 16:00:47
Meanwhile I managed to fix it myself by using a subquery:


SELECT
foo.[QuoteNumber]
,foo.[QuoteRevNo]
,ql.*

FROM (
SELECT
[QuoteNumber]
,MAX([QuoteRevNo]) as [QuoteRevNo]
FROM
test200
GROUP BY [QuoteNumber]
) foo
INNER JOIN test200 ql
ON foo.[QuoteNumber] = ql.[QuoteNumber]
AND foo.[QuoteRevNo] = ql.[QuoteRevNo]



This works, so my issue is solved.
Thanks again for your help.
Go to Top of Page

Keeravi123
Starting Member

1 Post

Posted - 2014-08-20 : 08:47:48
SELECT *, RANK() OVER( ORDER BY QUOTEREVNO DESC)AS Top_REV_NO
FROM Table

Ravi
Go to Top of Page
   

- Advertisement -