February 12, 2025 at 5:50 pm
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 13, 2025 at 6:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
February 16, 2025 at 4:50 am
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 )
February 24, 2025 at 6:44 am
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;
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy