ROWID in MS SQL

• tizma

  SSC Enthusiast

  Points: 180

  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 )

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  February 13, 2025 at 6:10 pm

  #4536945

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

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

• moideen12542

  Grasshopper

  Points: 12

  More actions

  February 16, 2025 at 4:50 am

  #4538709

  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 4 months, 1 week ago by moideen12542.
  • This reply was modified 4 months, 1 week ago by moideen12542.
  • This reply was modified 4 months, 1 week ago by moideen12542.
  • This reply was modified 4 months, 1 week ago by moideen12542.

• Faustalker

  Grasshopper

  Points: 11

  More actions

  February 24, 2025 at 6:44 am

  #4544627

  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;