Solved

Row_number(), SQL 2005 Returning different results each time

Posted on 2010-08-16
13
418 Views
Last Modified: 2012-05-10
Hi
i have the following query wich i use Row_Number() for pagination

the problem is that each time it return different results
am i missing something???
SELECT * FROM 



(



SELECT ROW_NUMBER() OVER (ORDER BY LastDateUpdate desc) 

AS Row,

dbo.Contractor_Names.ContractorName,dbo.ContractorTypes.ContractorTypeName,

dbo.ContractorPassSystem.* FROM dbo.ContractorPassSystem 

INNER JOIN 

dbo.ContractorTypes ON 

dbo.ContractorPassSystem.ContractorTypeID = dbo.ContractorTypes.ContractorTypeID 

left outer JOIN dbo.MainContractor ON 

dbo.ContractorPassSystem.MainContractorID = dbo.MainContractor.MainContractorID 

left outer JOIN 

dbo.Contractor_Names ON 

dbo.MainContractor.ContractorNameID = dbo.Contractor_Names.ContractorNameID



 WHERE (dbo.ContractorPassSystem.isDisable = 0)) AS EMP 

WHERE Row BETWEEN    1 and 20

Open in new window

0
Comment
Question by:Kamal Khaleefa
  • 3
  • 3
  • 3
  • +3
13 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33443859
>each time it return different results
can you clarify? note that if you have the same value for "LastDateUpdate", and no further clarification for the order, the order might indeed change.
also, which column the column is coming from? you might want to clarify the table it comes from...
0
 
LVL 1

Expert Comment

by:Deyhim
ID: 33443875
LastDateUpdate  is changed i think , check it
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 33443876
And I suspect you're using SQL Server 2008 as I don't think row_number() is supported in 2005.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 33443885
>> I don't think row_number() is supported in 2005<<

are you sure?
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 33443893
Ignore me... had the syntax wrong
0
 
LVL 16

Author Comment

by:Kamal Khaleefa
ID: 33443911
Hi
im using SQL 2005 and it support Row_number()

LastDateUpdate   is a date coloum represent last date of the row changed

this query is executed on a table containing more than 150 000 Row
the problem when i run the query it execute and return the rows between 1 and 20
then run the same query again it return different data
should I Reset row number or what?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 33443930
>LastDateUpdate   is a date coloum represent last date of the row changed
so, many rows can have exactly the same value? is there no "time" portion ?

you can try this:


SELECT ROW_NUMBER() OVER (ORDER BY LastDateUpdate desc, some_id_column_that_is_unique_by_the_query_results ) 

Open in new window

0
 
LVL 1

Expert Comment

by:revteam
ID: 33443971
Re write the query as follwing and check

SELECT *,
      ROW_NUMBER() OVER (ORDER BY LastDateUpdate desc) AS Row,      
 FROM
(

SELECT LastDateUpdate
dbo.Contractor_Names.ContractorName,dbo.ContractorTypes.ContractorTypeName,
dbo.ContractorPassSystem.* FROM dbo.ContractorPassSystem
INNER JOIN
dbo.ContractorTypes ON
dbo.ContractorPassSystem.ContractorTypeID = dbo.ContractorTypes.ContractorTypeID
left outer JOIN dbo.MainContractor ON
dbo.ContractorPassSystem.MainContractorID = dbo.MainContractor.MainContractorID
left outer JOIN
dbo.Contractor_Names ON
dbo.MainContractor.ContractorNameID = dbo.Contractor_Names.ContractorNameID

 WHERE (dbo.ContractorPassSystem.isDisable = 0)) AS EMP
WHERE Row BETWEEN    1 and 20
0
 
LVL 16

Author Comment

by:Kamal Khaleefa
ID: 33444079
revteam
the order of writing the query should be as the one i write
i try ur query and i got the following error

Invalid column name 'Row'.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33444086
did you try my suggestion?
0
 
LVL 1

Expert Comment

by:revteam
ID: 33444201
Try this

;with ctecontractor As
(
SELECT *,
      ROW_NUMBER() OVER (ORDER BY LastDateUpdate desc) AS Row      
 FROM
(

SELECT LastDateUpdate
dbo.Contractor_Names.ContractorName,dbo.ContractorTypes.ContractorTypeName,
dbo.ContractorPassSystem.* FROM dbo.ContractorPassSystem
INNER JOIN
dbo.ContractorTypes ON
dbo.ContractorPassSystem.ContractorTypeID = dbo.ContractorTypes.ContractorTypeID
left outer JOIN dbo.MainContractor ON
dbo.ContractorPassSystem.MainContractorID = dbo.MainContractor.MainContractorID
left outer JOIN
dbo.Contractor_Names ON
dbo.MainContractor.ContractorNameID = dbo.Contractor_Names.ContractorNameID

 WHERE (dbo.ContractorPassSystem.isDisable = 0)) AS EMP )

Select * from ctecontractor where Row     Row BETWEEN    1 and 20  
0
 
LVL 1

Expert Comment

by:revteam
ID: 33444203
;with ctecontractor As
(
SELECT *,
      ROW_NUMBER() OVER (ORDER BY LastDateUpdate desc) AS Row      
 FROM
(

SELECT LastDateUpdate
dbo.Contractor_Names.ContractorName,dbo.ContractorTypes.ContractorTypeName,
dbo.ContractorPassSystem.* FROM dbo.ContractorPassSystem
INNER JOIN
dbo.ContractorTypes ON
dbo.ContractorPassSystem.ContractorTypeID = dbo.ContractorTypes.ContractorTypeID
left outer JOIN dbo.MainContractor ON
dbo.ContractorPassSystem.MainContractorID = dbo.MainContractor.MainContractorID
left outer JOIN
dbo.Contractor_Names ON
dbo.MainContractor.ContractorNameID = dbo.Contractor_Names.ContractorNameID

 WHERE (dbo.ContractorPassSystem.isDisable = 0)) AS EMP )

Select * from ctecontractor where Row  BETWEEN    1 and 20  
0
 
LVL 16

Author Closing Comment

by:Kamal Khaleefa
ID: 33444350
well
this solution help me to solve my problem
since i has alot of rows then some of them has the same data time so data was different
i add another colum as a unique coloum in the query
Thanks
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now