Solved

Row_number(), SQL 2005 Returning different results each time

Posted on 2010-08-16
13
420 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

911 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

17 Experts available now in Live!

Get 1:1 Help Now