to find equal and opposite rows in a table
-
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?
-
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 -
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) -
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
-
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;
-
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 🙂 !!!
-
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply