?
Solved

Row_number(), SQL 2005 Returning different results each time

Posted on 2010-08-16
13
Medium Priority
?
445 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
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…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

621 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