ROWID in MS SQL

Question

Post reply

ROWID in MS SQL

tizma

SSC Enthusiast

Points: 180
More actions
February 12, 2025 at 5:51 pm

#4536184

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 )

Viewing 5 posts - 1 through 4 (of 4 total)

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

Phil Parkin SSC Guru Points: 247095 More actions · Answer 1

Why have you posted a random query? Please ask a question.

tizma SSC Enthusiast Points: 180 More actions · Answer 2

How to rewrite a query in MS SQL:

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 )

pietlinden SSC Guru Points: 63549 More actions · Answer 3

Could we at least have some CREATE TABLE scripts and some INSERT scripts? I'm not feeling that creative today.

What's wrong with the query you have? Does it not work? Or is it really slow? If it's slow but works, then there may be a better way of writing it or a better way of indexing the tables it's using.

Jeff Moden SSC Guru Points: 1004546 More actions · Answer 4

tizma wrote:

How to rewrite a query in MS SQL:
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 )

What dialect of SQL allows a query to start with a WHERE clause?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ROWID in MS SQL

Cookies on SQLServerCentral