CODEWITHSUNDEEP
sqlsql-servert-sql
Tom
Tom

Reputation: 293

SQL Server: How to get Row Number in multiple Select

I get wrong error row number when i use Multiple Select

First Query gets right row number

SELECT num
FROM (SELECT ItemId, CommentId,ROW_NUMBER() OVER (ORDER BY CreatedDate desc) AS num
      From ItemComments
      where ItemId = 10
    ) AS numbered
WHERE numbered.CommentId =100

Scond Query gets right row number

SELECT num
FROM (SELECT ItemId, CommentId, ROW_NUMBER() OVER(ORDER BY CreatedDate desc) AS num
      From ItemComments
      where ItemId = 20
    ) AS numbered
WHERE numbered.CommentId =200

When i tried to use both above values in one query i got wrong row number

Third Query which gets wrong row number

SELECT num
FROM (SELECT ItemId, CommentId, ROW_NUMBER() OVER(ORDER BY createddate desc) AS num
      From ItemComments
      where ItemId in(20,10)
     ) AS numbered
WHERE numbered.CommentId in (200, 100)

Please help me to get the right row number in Multiple Select

Thank you

enter image description here

Upvotes: 1

Views: 369

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

You just need partition by:

SELECT num
FROM (SELECT ItemId, CommentId,
            ROW_NUMBER() OVER (PARTITION BY ItemId ORDER BY createddate desc) AS num
      From ItemComments
      where ItemId in (20, 10)
     ) numbered
WHERE numbered.CommentId in (200, 100)

Upvotes: 2

Related Questions