Sql Select Query Row number not giving desired Results
Sql - ; WITH ct AS ( SELECT COUNT(*) ct FROM [Vendor Tool input] WHERE [VDMResource] LIKE '%bamola%' ), rcr AS ( SELECT * FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY [VDMResource] ORDER BY [VDMResource] DESC ) AS SrNo , * FROM [Vendor Tool Input] ) AS VendorToolInput WHERE [VDMResource] LIKE '%bamola%' ) SELECT * FROM ct , rcr WHERE rcr.[File Name] NOT IN ( SELECT [File Name] FROM VARS1B ) Data: Table Name - Vendor Tool Input **VendorName** INCORPORATED LTD TIEN LIMITED XYZ Table Name - VARS1B **VendorName** INCORPORATED Current Output SrNo VendorName 2 LTD 3 TIEN 4 LIMITED 5 XYZ Desired Output SrNo VendorName 1 LTD 2 TIEN 3 LIMITED 4 XYZ
Your row_number is in the wrong place. You create a numbered set, then later use a where clause to filter this set. So this is why you're not getting a full, sequentially numbered output. Your query also looks a bit over-engineered. If you post sample data and DDL, as per Tom's request, I'm sure that someone would be able to give you something a bit better.