SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Filter table on highest revision number
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ArnoldG
Starting Member

Netherlands
29 Posts

Posted - 08/19/2014 :  10:27:19  Show Profile  Reply with Quote
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

United Kingdom
58 Posts

Posted - 08/19/2014 :  11:31:10  Show Profile  Reply with Quote
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

Netherlands
29 Posts

Posted - 08/19/2014 :  13:56:17  Show Profile  Reply with Quote
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

Netherlands
29 Posts

Posted - 08/19/2014 :  16:00:47  Show Profile  Reply with Quote
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

USA
1 Posts

Posted - 08/20/2014 :  08:47:48  Show Profile  Reply with Quote
SELECT *, RANK() OVER( ORDER BY QUOTEREVNO DESC)AS Top_REV_NO
FROM Table

Ravi
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000