InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 20:07:23 GMT20http://www.sqlservercentral.com/Forums/Topic1411734-391-1.aspx[code="sql"]-- 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')) aWHERE a.[rank] <=5 AND a.CUSTOMER_NUMBER = '110000053' -- Test it:SELECT TOP 5 * FROM EMEA_SERVICEMAX.Servicemax.SERVICE_CONTRACTS_STGWHERE 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 componentSELECT @CutoffDate = DATEADD(day,DATEDIFF(day,0,GETDATE()-730),0)[/code]Mon, 28 Jan 2013 01:21:18 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1411734-391-1.aspxTry changing select csa.* ,ROW_NUMBER() over (Partition BY csa.customer_number order by csa.erp_id,csa.ANNUAL_CONTRACT_PRICE desc) as Ranktoselect csa.* ,ROW_NUMBER() over (Partition BY csa.customer_number order by csa.ANNUAL_CONTRACT_PRICE, csa.erp_id desc) as RankSo that if you have a draw on the ANNUAL_CONTRACT_PRICE, then the erp_id will ensure consistency in the ordering.Mon, 28 Jan 2013 01:05:29 GMTDavid McKinneyhttp://www.sqlservercentral.com/Forums/Topic1411734-391-1.aspxMy 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 Rankfrom EMEA_SERVICEMAX.Servicemax.SERVICE_CONTRACTS_STG csa where csa.CONTRACT_START_DATE >= GETDATE()-730 and csa.CONTRACT_STATUS_DESCRIPTION not in ('Free','Canceled','Cancelled')) awhere a.rank <=5 and a.CUSTOMER_NUMBER='110000053'Result: ERP_ID CUSTOMER_NUMBER ANNUAL_CONTRACT_PRICE RankC10003890-axq2011-178 110000053 1963.94 1C10003778-axq2011-178 110000053 720.13 2C10003775-axq2011-178 110000053 639.39 3C10003886-axq2011-178 110000053 591.87 4C10003847-axq2011-178 110000053 579.68 5By 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 [b] csa.erp_id,csa.ANNUAL_CONTRACT_PRICE[/b] desc) as Rankfrom EMEA_SERVICEMAX.Servicemax.SERVICE_CONTRACTS_STG csa where csa.CONTRACT_START_DATE >= GETDATE()-730 and csa.CONTRACT_STATUS_DESCRIPTION not in ('Free','Canceled','Cancelled')) awhere a.rank <=5 and a.CUSTOMER_NUMBER='110000053'Result :ERP_ID CUSTOMER_NUMBER ANNUAL_CONTRACT_PRICE RankC10003731-axq2011-178 110000053 348 1C10003752-axq2011-178 110000053 559.84 2C10003753-axq2011-178 110000053 536.13 3C10003754-axq2011-178 110000053 545.16 4C10003755-axq2011-178 110000053 457.45 5Plz help me?Mon, 28 Jan 2013 01:00:11 GMTnil.hajarehttp://www.sqlservercentral.com/Forums/Topic1411734-391-1.aspxI 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.Fri, 25 Jan 2013 14:04:33 GMTbrendan woulfehttp://www.sqlservercentral.com/Forums/Topic1411734-391-1.aspxI 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!)Fri, 25 Jan 2013 08:58:56 GMTDavid McKinneyhttp://www.sqlservercentral.com/Forums/Topic1411734-391-1.aspxWithout 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_NUMBERFri, 25 Jan 2013 07:51:47 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1411734-391-1.aspxHi 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 Rankfrom EMEA_SERVICEMAX.Servicemax.SERVICE_CONTRACTS_STG where CONTRACT_START_DATE >= GETDATE()-730 and CONTRACT_STATUS_DESCRIPTION not in ('Free','Canceled','Cancelled') ) awhere a.rank <=5 union allselect b.ERP_ID from (select *,ROW_NUMBER() over (Partition BY customer_number order by CONTRACT_START_DATE desc) as Rankfrom EMEA_SERVICEMAX.Servicemax.SERVICE_CONTRACTS_STG where CONTRACT_START_DATE >= GETDATE()-730 and CONTRACT_STATUS_DESCRIPTION not in ('Free','Canceled','Cancelled') ) bwhere b.rank <=5 ) Fri, 25 Jan 2013 07:45:02 GMTnil.hajare