CODEWITHSUNDEEP
sql-server
Concerned_Citizen
Concerned_Citizen

Reputation: 6835

Subquery Results Differ When Selecting Entire Group

I am working with the following query in SQL Server 2012.

When I run this I get the following.

However if I run this for all the Antibacterials, commenting out 

AND THERAPEUTIC_CLASS_NAME IN ('Beta-lactam, Penicillins', 'Beta-lactam, Cephalosporins')

and making no other changes, the numbers don't match. I have been pulling my hair over why the results differ but to no avail. I appreciate any advice on this.

When selecting at a higher level.

Upvotes: 0

Views: 39

Answers (1)

shawnt00
shawnt00

Reputation: 17915

The problem is in the row_number expression.

The same NDC can appear under multiple therapeutic classes but the row_numbering is not deterministic. Removing the filter caused many of those rows from the old query to get a different numbering and thus moved under a different category.

I'm not sure whether row_number is an appropriate fix to eliminate these duplicates but if it is then you'll have to add more ordering columns. (Ordering on the same column as the partition doesn't do anything either.)

Upvotes: 1

Related Questions