Filter table on highest revision number

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]

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?

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.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources