asked
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
I'm not even sure how you are getting your current output given what you have there. Can you please give us full sample data for your inputs and exactly what you are trying to do? Your final SELECT does a Cartesian product on ct and rcr, and I don't know where file name came from.
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.
@baimzz @iainrobertson is absolutely correct, the query is clearly over-engineered. Additionally, it is rather strange that you need to include the **ct** column in it which is the count of matching rows but for whatever reason you need this exact same value to be included with every row in the final result. Without an attempt to improve this query in any way, here is the version which will at least produce the expected results. It is based on @iainrobertson recommendation to NOT create a numbered set in the wrong place:
;with ct AS
(
select count(*) ct
from [Vendor Tool input]
where [VDMResource] like '%bamola%'
),
rcr AS
(
select
row_number() over(partition by [VDMResource]
order by [VDMResource] desc) SrNo, *
from [Vendor Tool Input]
where
[VDMResource] LIKE '%bamola%'
and [File Name] not in (select [File Name] from VARS1B)
)
select * from ct cross join rcr;
16 People are following this question.
