question

baimzz avatar image
baimzz 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
sql-serverqueryselect
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·

1 Answer

· Write an Answer
iainrobertson avatar image
iainrobertson answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@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;
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.