Solved

Row_number(), SQL 2005 Returning different results each time

Posted on 2010-08-16
13
426 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +3
13 Comments
 
LVL 143

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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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
 
LVL 143

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 143

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

627 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