ROWID in MS SQL

Question

Post reply

ROWID in MS SQL

tizma

Newbie

Points: 0
More actions
February 12, 2025 at 5:50 pm

#4536183

How to rewrite a query in MS SQL, please?
WHERE a.ROWID IN (SELECT rid
FROM ( SELECT ROWID rid,
row_number() OVER (PARTITION BY c.id ORDER BY c.id) rn
FROM details c
WHERE a.id = c.id
AND c.id > ( SELECT max(id)
FROM details d
WHERE d.id = c.id
AND d.cd = '2030')
AND c.cd IN ( '2017','2012','2021'))
WHERE rn = 1 )
February 14, 2025 at 11:15 am

This was removed by the editor as SPAM
February 14, 2025 at 12:26 pm

This was removed by the editor as SPAM

February 26, 2025 at 8:52 am

This was removed by the editor as SPAM

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply

Site Owners Newbie Points: 0 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

moideen12542 Newbie Points: 0 More actions · Answer 2

In MS SQL Server, there is no direct equivalent to Oracle’s ROWID.

However, there are several alternatives depending on what you need to achieve:

WHERE a.id IN ( SELECT id FROM ( SELECT c.id, ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY c.id) AS rn FROM details c WHERE a.id = c.id AND c.id > ( SELECT MAX(id) FROM details d WHERE d.id = c.id AND d.cd = '2030' ) AND c.cd IN ('2017', '2012', '2021') ) AS subquery WHERE rn = 1 )

This reply was modified 1 month, 1 week ago by moideen12542.
This reply was modified 1 month ago by moideen12542.
This reply was modified 1 month ago by moideen12542.
This reply was modified 1 month ago by moideen12542.

Faustalker Newbie Points: 0 More actions · Answer 3

Here's how to rewrite the query in a concise and easy-to-understand way in MS SQL:

WITH CTE AS (
    SELECT 
        c.ROWID AS rid,
        ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY c.id) AS rn
    FROM details c
    WHERE c.id > ( 
        SELECT MAX(d.id)
        FROM details d
        WHERE d.id = c.id AND d.cd = '2030'
    )
    AND c.cd IN ('2017', '2012', '2021')
)
SELECT a.*
FROM your_table a
JOIN CTE ON a.ROWID = CTE.rid
WHERE CTE.rn = 1 AND a.id = CTE.id;