"Murphy was an optimist!"
SQL Query Conundrum June 17, 2013 9:43 amPosted by Doug McCaughan in : MS SQL, Programming, Technology
Here’s a interesting SQL challenge:
Say you have a query returning a result set that in some cases one row cancels out another. For example, let’s say there are 5 columns: A B C D E. One result possibility is: row 1- A B C 4 E with row 2- Z Y X 9 E Naturally those are completely separate data elements. But when row 1 is A B C 4 E and row 2 is A B C -4 E row two is a data correction to row 1 (think double entry accounting) and row 1 and row 2 need to be excluded from the result set. So, right now if the result set returned 20 rows and had the situation above, we really only want to be returning 18 rows. What’s a good approach to this?
Here’s a better representation of the problem:
Row Name Account Rank Value Status
- G H I 5 J
- A B C 4 E
- A B C -4 E
- Z Y C 4 E
- A B C 7 E
- Z Y C 22 E
We really want to return rows 1, 4, 5, and 6.