Improve company productivity with a Business Account.Sign Up

x
?
Solved

Row_number(), SQL 2005 Returning different results each time

Posted on 2010-08-16
13
Medium Priority
?
459 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 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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
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 2000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

569 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