Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Unexpected behaviour of query Expand / Collapse
Author
Message
Posted Friday, January 25, 2013 7:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 5:49 AM
Points: 4, Visits: 11
Hi All,

I am using below query in sql server to find out top 5 amount and date by customer.

I have used union for the same. But it gives different counts when i ran this query again and again. Sometimes it shows count as 69071 and sometimes it 69072 with same query.

Can you please help me ?

Query:

SELECT COUNT(*)
FROM EMEA_SERVICEMAX.Servicemax.SERVICE_CONTRACTS_STG c

where (CONTRACT_STATUS_DESCRIPTION !='Active')
and erp_id not in ( select
a.ERP_ID
from
(select * ,ROW_NUMBER() over (Partition BY customer_number order by ANNUAL_CONTRACT_PRICE desc) as Rank
from EMEA_SERVICEMAX.Servicemax.SERVICE_CONTRACTS_STG where CONTRACT_START_DATE >= GETDATE()-730
and CONTRACT_STATUS_DESCRIPTION not in ('Free','Canceled','Cancelled')


) a
where a.rank <=5
union all
select
b.ERP_ID
from
(select *,ROW_NUMBER() over (Partition BY customer_number order by CONTRACT_START_DATE desc) as Rank
from EMEA_SERVICEMAX.Servicemax.SERVICE_CONTRACTS_STG where CONTRACT_START_DATE >= GETDATE()-730
and CONTRACT_STATUS_DESCRIPTION not in ('Free','Canceled','Cancelled')


) b
where b.rank <=5
)


Post #1411734
Posted Friday, January 25, 2013 7:51 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:45 AM
Points: 1,694, Visits: 19,552

Without sample data it's not easy to tell, however, you're using ROW_NUMBER which can be non-deterministic and can give inconsistent results.
Try using RANK or DENSE_RANK instead of ROW_NUMBER


____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1411739
Posted Friday, January 25, 2013 8:58 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 4:02 AM
Points: 648, Visits: 1,874
I agree that the problem's going to be connected to your row_number() and the fact that the field used in the Order by section of the OVER clause i.e. ANNUAL_CONTRACT_PRICE and/or CONTRACT_START_DATE are not unique for a customer.

If you can add the other elements of the primary key into the 2 Order By's, then you should be OK. (I think!)
Post #1411778
Posted Friday, January 25, 2013 2:04 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 11, 2014 9:26 AM
Points: 208, Visits: 1,032
I was thinking it's the GETDATE()-730 part. That's non-deterministic and might explain the different results as it's part of your where clause.
Post #1411931
Posted Monday, January 28, 2013 1:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 5:49 AM
Points: 4, Visits: 11
My primary key for table is erp_id and service_contract_number. I have added the erp_id in order by clause. but my top records shows wrong.
e.g. Top records by customer with annual_contract_price.

select
ERP_ID ,CUSTOMER_NUMBER,ANNUAL_CONTRACT_PRICE,Rank
from
(select csa.* ,ROW_NUMBER() over (Partition BY csa.customer_number order by csa.ANNUAL_CONTRACT_PRICE desc) as Rank
from EMEA_SERVICEMAX.Servicemax.SERVICE_CONTRACTS_STG csa where csa.CONTRACT_START_DATE >= GETDATE()-730
and csa.CONTRACT_STATUS_DESCRIPTION not in ('Free','Canceled','Cancelled')) a
where a.rank <=5 and a.CUSTOMER_NUMBER='110000053'

Result:

ERP_ID CUSTOMER_NUMBER ANNUAL_CONTRACT_PRICE Rank
C10003890-axq2011-178 110000053 1963.94 1
C10003778-axq2011-178 110000053 720.13 2
C10003775-axq2011-178 110000053 639.39 3
C10003886-axq2011-178 110000053 591.87 4
C10003847-axq2011-178 110000053 579.68 5

By Adding erp_id in order by clause:
result is defferent.

select
ERP_ID ,CUSTOMER_NUMBER,ANNUAL_CONTRACT_PRICE,Rank
from
(select csa.* ,ROW_NUMBER() over (Partition BY csa.customer_number order by
csa.erp_id,csa.ANNUAL_CONTRACT_PRICE desc) as Rank
from EMEA_SERVICEMAX.Servicemax.SERVICE_CONTRACTS_STG csa where csa.CONTRACT_START_DATE >= GETDATE()-730
and csa.CONTRACT_STATUS_DESCRIPTION not in ('Free','Canceled','Cancelled')) a
where a.rank <=5 and a.CUSTOMER_NUMBER='110000053'

Result :

ERP_ID CUSTOMER_NUMBER ANNUAL_CONTRACT_PRICE Rank
C10003731-axq2011-178 110000053 348 1
C10003752-axq2011-178 110000053 559.84 2
C10003753-axq2011-178 110000053 536.13 3
C10003754-axq2011-178 110000053 545.16 4
C10003755-axq2011-178 110000053 457.45 5

Plz help me?
Post #1412219
Posted Monday, January 28, 2013 1:05 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 4:02 AM
Points: 648, Visits: 1,874
Try changing

select csa.* ,ROW_NUMBER() over (Partition BY csa.customer_number order by
csa.erp_id,csa.ANNUAL_CONTRACT_PRICE desc) as Rank

to

select csa.* ,ROW_NUMBER() over (Partition BY csa.customer_number order by
csa.ANNUAL_CONTRACT_PRICE, csa.erp_id desc) as Rank

So that if you have a draw on the ANNUAL_CONTRACT_PRICE, then the erp_id will ensure consistency in the ordering.
Post #1412220
Posted Monday, January 28, 2013 1:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
-- This looks correct:
SELECT
ERP_ID,
customer_number,
ANNUAL_CONTRACT_PRICE,
[Rank]
FROM (
SELECT
ERP_ID,
customer_number,
ANNUAL_CONTRACT_PRICE,
ROW_NUMBER() OVER (PARTITION BY customer_number ORDER BY ANNUAL_CONTRACT_PRICE DESC) AS [Rank]
FROM EMEA_SERVICEMAX.Servicemax.SERVICE_CONTRACTS_STG
WHERE CONTRACT_START_DATE >= GETDATE()-730
AND CONTRACT_STATUS_DESCRIPTION NOT IN ('Free','Canceled','Cancelled')
) a
WHERE a.[rank] <=5
AND a.CUSTOMER_NUMBER = '110000053'

-- Test it:
SELECT TOP 5 *
FROM EMEA_SERVICEMAX.Servicemax.SERVICE_CONTRACTS_STG
WHERE CONTRACT_START_DATE >= GETDATE()-730
AND CONTRACT_STATUS_DESCRIPTION NOT IN ('Free','Canceled','Cancelled')
AND a.CUSTOMER_NUMBER = '110000053'
ORDER BY ANNUAL_CONTRACT_PRICE DESC

-- Suggestions:
-- Use a variable for the cutoff date and remove the time component
SELECT @CutoffDate = DATEADD(day,DATEDIFF(day,0,GETDATE()-730),0)



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1412221
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse