jump to navigation

NOTE: The spam filter is being unusually aggressive. If you comment does not immediately appear, it has simply been placed in moderation and I will approve it as quickly as possible. Thank you for your patience.

"Murphy was an optimist!"

SQL Query Conundrum June 17, 2013 9:43 am

Posted by Doug McCaughan in : MS SQL, Programming, Technology
, trackback

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

  1. G H I 5 J
  2. A B C 4 E
  3. A B C -4 E
  4. Z Y C 4 E
  5. A B C 7 E
  6. Z Y C 22 E

We really want to return rows 1, 4, 5, and 6.

Comments after advertisement


no comments yet - be the first?