Modify my SQL Server query -- returns too many rows sometimes

speed up mysql queries large tables
how to make select query faster in sql server
mysql query optimization techniques
how to check query performance in mysql
how to search millions of record in sql table faster?
query optimization in mysql with example
how to optimize sql query with multiple joins
sql server query performance

I need to update the following query so that it only returns one child record (remittance) per parent (claim).

Table Remit_To_Activate contains exactly one date/timestamp per claim, which is what I wanted.

But when I join the full Remittance table to it, since some claims have multiple remittances with the same date/timestamps, the outermost query returns more than 1 row per claim for those claim IDs.

SELECT * FROM REMITTANCE 
WHERE BILLED_AMOUNT>0 AND ACTIVE=0 
AND REMITTANCE_UUID IN ( 
  SELECT REMITTANCE_UUID FROM Claims_Group2 G2
  INNER JOIN Remit_To_Activate t ON (
    (t.ClaimID = G2.CLAIM_ID) AND 
    (t.DATE_OF_LATEST_REGULAR_REMIT = G2.CREATE_DATETIME)
)
  where ACTIVE=0 and BILLED_AMOUNT>0
)

I believe the problem would be resolved if I included REMITTANCE_UUID as a column in Remit_To_Activate. That's the REAL issue. This is how I created the Remit_To_Activate table (trying to get the most recent remittance for a claim):

SELECT MAX(create_datetime) as DATE_OF_LATEST_REMIT,
MAX(claim_id) AS ClaimID,
INTO Latest_Remit_To_Activate 
FROM Claims_Group2
WHERE BILLED_AMOUNT>0
GROUP BY Claim_ID 
ORDER BY Claim_ID

Claims_Group2 contains these fields: REMITTANCE_UUID, CLAIM_ID, BILLED_AMOUNT, CREATE_DATETIME

Here are the 2 rows that are currently giving me the problem--they're both remitts for the SAME CLAIM, with the SAME TIMESTAMP. I only want one of them in the Remits_To_Activate table, so only ONE remittance will be "activated" per Claim: enter image description here


You can change your query like this:

SELECT
  p.*, latest_remit.DATE_OF_LATEST_REMIT
FROM
  Remittance AS p inner join
(SELECT MAX(create_datetime) as DATE_OF_LATEST_REMIT,
claim_id,
FROM Claims_Group2
WHERE BILLED_AMOUNT>0
GROUP BY Claim_ID 
ORDER BY Claim_ID) as latest_remit
on latest_remit.claim_id = p.claim_id;

This will give you only one row. Untested (so please run and make changes).

4. Query Performance Optimization, Modify my SQL Server query — returns too many rows sometimes Here are the 2 rows that are currently giving me the problem--they're both  When working with databases, sometimes you want to find out how many rows will be returned by a query, without actually returning the results of the query. Or sometimes you might just want to find out how many rows are in a given table. In SQL Server, you can use T-SQL ‘s COUNT () function to return the number of rows that would be returned


Without having more information on the structure of your database -- especially the structure of Claims_Group2 and REMITTANCE, and the relationship between them, it's not really possible to advise you on how to introduce a remittance UUID into DATE_OF_LATEST_REMIT.

Since you are using SQL Server, however, it is possible to use a window function to introduce a synthetic means to choose among remittances having the same timestamp. For example, it looks like you could approach the problem something like this:

select *
from (
  select
    r.*,
    row_number() over (partition by cg2.claim_id order by cg2.create_datetime desc) as rn
  from
    remittance r
    join claims_group2 cg2
      on r.remittance_uuid = cg2.remittance_uuid
  where
    r.active = 0 
    and r.billed_amount > 0
    and cg2.active = 0
    and cg2.billed_amount > 0
  ) t
where t.rn = 1

Note that that that does not depend on your DATE_OF_LATEST_REMIT table at all, it having been subsumed into the inline view. Note also that this will introduce one extra column into your results, though you could avoid that by enumerating the columns of table remittance in the outer select clause.

It also seems odd to be filtering on two sets of active and billed_amount columns, but that appears to follow from what you were doing in your original queries. In that vein, I urge you to check the results carefully, as lifting the filter conditions on cg2 columns up to the level of the join to remittance yields a result that may return rows that the original query did not (but never more than one per claim_id).

Top 5 Reasons for Wrong Results in SQL Server, We then dig much deeper into query optimization and server internals. how MySQL executes a particular query, and you'll learn how to change the query execution plan. Beware of placing too much emphasis on query execution time​. This query will return 10 rows, and EXPLAIN shows that MySQL uses  Note that even though SQL Server auto-parameterized the query (that’s the @1 part at the top), SQL Server chose a different execution plan. This time, the actual plan shows that SQL Server expected 3mm rows to come back – so here, it makes more sense to do a clustered index scan rather than first make a list of the users that match, then do 3mm key lookups to get the SELECT * part.


A co-worker offered me this elegant demonstration of a solution. I'd never used "over" or "partition" before. Works great! Thank you John and Gaurasvsa for your input.

if OBJECT_ID('tempdb..#t') is not null
drop table #t

select *, ROW_NUMBER() over (partition by CLAIM_ID order by CLAIM_ID) as ROW_NUM
into #t
from 
(
    select '2018-08-15 13:07:50.933' as CREATE_DATE, 1 as CLAIM_ID, NEWID() as 
    REMIT_UUID
    union select '2018-08-15 13:07:50.933', 1, NEWID()
    union select '2017-12-31 10:00:00.000', 2, NEWID()
) x 

select * 
from #t
order by CLAIM_ID, ROW_NUM

select CREATE_DATE, MAX(CLAIM_ID), MAX(REMIT_UUID)
from #t
where ROW_NUM = 1
group by CREATE_DATE

More efficient SQL with query planning and optimization (article , Many people have come across situations where their queries are returning SQL queries are often constructed within application code or The following can never return the wrong results (e.g. will always return all LastName FROM dbo.​x WHERE LastName = @ln_varchar; -- 0 rows View all my tips. Holger Schmeling is a SQL Server Consultant based in Munich, Germany. He has been working with SQL Server since 1996 (Version 6.5) and has used it in many projects as a developer, administrator, database architect and designer. Occasionally he also gives classes on SQL Server development, administration and tuning.


Ten Common SQL Programming Mistakes, Do a "full table scan": look at every single row in the table, return the matching rows. If we were executing that query many times (more than 23 times) or if we Sometimes, you might discover a badly performing query after it takes so long that it into different SQL query planners that I found interesting: SQL Server Query  The SQL Server plan cache is the receptacle in which the plan from the output of the query optimizer will be placed. In SQL Server 2005 and new versions, the Dynamic Management View named sys.dm_exec_cached_plans exists to allow the DBA to interrogate the plan cache and pull relevant statistics from it to illuminate the patterns in which plans


Why MySQL Could Be Slow With Large Tables?, The examples are presented using SQL Server's Transact-SQL dialect, but You may have been expecting this query to return two rows (for 'black' and This mistake will often surface if requirements change, and a Some people seem to like them so much that they throw one into almost every query,  SQL Server performs sort, intersect, union, and difference operations using in-memory sorting and hash join technology. Using this type of query plan, SQL Server supports vertical table partitioning, sometimes called columnar storage. SQL Server employs four types of join operations: Nested Loops joins. Adaptive joins (Starting with SQL Server


Performance Tuning SQL Queries, Peter Zaitsev explains how to optimize your MySQL table design MySQL has installations with many billions of rows and delivers great +------------+ So if you're dealing with large data sets and complex queries here are few tips. are often faster than range scans and other types of index lookups. What is a SQL Server Cursor. A SQL Server cursor is a set of T-SQL logic to loop over a predetermined number of rows one at a time. The purpose for the cursor may be to update one row at a time or perform an administrative process such as SQL Server database backups in a sequential manner.