to find equal and opposite rows in a table

Question

to find equal and opposite rows in a table

srikantchary4u

SSC Enthusiast

Points: 125
More actions
May 25, 2015 at 11:31 am

#398500

How to find the equal and opposite rows in a table.
E.g.:
book position
A 500
B -500
C -500
The output should return the rows having equal and opposite positions.
O/P:
A 500
B -500
I have tried self join on a table but in vain.Do we need to cursors for these row level handling?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Y.B. SSChampion Points: 11649 More actions · Answer 1

Could you further explain why C is eliminated from your desired output? In fact we could use a little more info in general. Without knowing how A is related to B and/or C it's hard to look at your example and come up with a solution.

Taking this statement literally:

How to find the equal and opposite rows in a table.

Should mean that A, B and C are all returned because they all meet your criteria.

SELECT quote FROM brain WHERE original = 1
0 rows returned

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

yb751 (5/25/2015)
Could you further explain why C is eliminated from your desired output? In fact we could use a little more info in general. Without knowing how A is related to B and/or C it's hard to look at your example and come up with a solution.
Taking this statement literally:
How to find the equal and opposite rows in a table.
Should mean that A, B and C are all returned because they all meet your criteria.

Probably because A has already been "consumed" by B. Think of it like debits and credits in a checkbook or inventory control system.

--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)

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

Your requirements are a bit unclear but this may work for you

WITH CTE AS(

SELECT book,position,

ROW_NUMBER() OVER(PARTITION BY position ORDER BY book) AS rn

FROM mytable)

SELECT a.book, a.position

FROM CTE a

WHERE EXISTS(SELECT * FROM CTE b WHERE b.position = -a.position

AND b.rn = a.rn)

ORDER BY book;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

Quick single scan solution

😎

USE tempdb;

GO

SET NOCOUNT ON;

WITH XSAMPLE(book, position) AS

(SELECT * FROM (VALUES

('A', 500)

,('B',-500)

,('C',-500)

) AS X(book, position))

,BALANCE_STUFF AS

(

SELECT

XS.book

,XS.position

,SUM(XS.position) OVER

(

PARTITION BY (SELECT NULL)

ORDER BY XS.book

ROWS BETWEEN UNBOUNDED PRECEDING

AND CURRENT ROW

) AS RT_BALANCE

FROM XSAMPLE XS

)

SELECT

BS.book

,BS.position

FROM BALANCE_STUFF BS

WHERE BS.RT_BALANCE <> 0;

srikantchary4u SSC Enthusiast Points: 125 More actions · Answer 5

Thanks a million guys especially to Mark Cowne.

You made my day.

This is my first post to this forum and it worked like a charm..Thanks again 🙂 !!

Hoping to come up with lot more queries/topics for the discussion 🙂 !!!

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 6

srikantchary4u (5/25/2015)
Thanks a million guys especially to Mark Cowne.
You made my day.
This is my first post to this forum and it worked like a charm..Thanks again 🙂 !!
Hoping to come up with lot more queries/topics for the discussion 🙂 !!!

Thanks for the feedback!

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537