Row_number(), SQL 2005 Returning different results each time
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???
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
LastDateUpdate is changed i think , check it
And I suspect you're using SQL Server 2008 as I don't think row_number() is supported in 2005.
>> I don't think row_number() is supported in 2005<<
are you sure?
are you sure?
Ignore me... had the syntax wrong
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Re write the query as follwing and check
SELECT *,
ROW_NUMBER() OVER (ORDER BY LastDateUpdate desc) AS Row,
FROM
(
SELECT LastDateUpdate
dbo.Contractor_Names.Contr
dbo.ContractorPassSystem.*
INNER JOIN
dbo.ContractorTypes ON
dbo.ContractorPassSystem.C
left outer JOIN dbo.MainContractor ON
dbo.ContractorPassSystem.M
left outer JOIN
dbo.Contractor_Names ON
dbo.MainContractor.Contrac
WHERE (dbo.ContractorPassSystem.
WHERE Row BETWEEN 1 and 20
SELECT *,
ROW_NUMBER() OVER (ORDER BY LastDateUpdate desc) AS Row,
FROM
(
SELECT LastDateUpdate
dbo.Contractor_Names.Contr
dbo.ContractorPassSystem.*
INNER JOIN
dbo.ContractorTypes ON
dbo.ContractorPassSystem.C
left outer JOIN dbo.MainContractor ON
dbo.ContractorPassSystem.M
left outer JOIN
dbo.Contractor_Names ON
dbo.MainContractor.Contrac
WHERE (dbo.ContractorPassSystem.
WHERE Row BETWEEN 1 and 20
ASKER
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'.
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'.
did you try my suggestion?
Try this
;with ctecontractor As
(
SELECT *,
ROW_NUMBER() OVER (ORDER BY LastDateUpdate desc) AS Row
FROM
(
SELECT LastDateUpdate
dbo.Contractor_Names.Contr
dbo.ContractorPassSystem.*
INNER JOIN
dbo.ContractorTypes ON
dbo.ContractorPassSystem.C
left outer JOIN dbo.MainContractor ON
dbo.ContractorPassSystem.M
left outer JOIN
dbo.Contractor_Names ON
dbo.MainContractor.Contrac
WHERE (dbo.ContractorPassSystem.
Select * from ctecontractor where Row Row BETWEEN 1 and 20
;with ctecontractor As
(
SELECT *,
ROW_NUMBER() OVER (ORDER BY LastDateUpdate desc) AS Row
FROM
(
SELECT LastDateUpdate
dbo.Contractor_Names.Contr
dbo.ContractorPassSystem.*
INNER JOIN
dbo.ContractorTypes ON
dbo.ContractorPassSystem.C
left outer JOIN dbo.MainContractor ON
dbo.ContractorPassSystem.M
left outer JOIN
dbo.Contractor_Names ON
dbo.MainContractor.Contrac
WHERE (dbo.ContractorPassSystem.
Select * from ctecontractor where Row Row BETWEEN 1 and 20
;with ctecontractor As
(
SELECT *,
ROW_NUMBER() OVER (ORDER BY LastDateUpdate desc) AS Row
FROM
(
SELECT LastDateUpdate
dbo.Contractor_Names.Contr
dbo.ContractorPassSystem.*
INNER JOIN
dbo.ContractorTypes ON
dbo.ContractorPassSystem.C
left outer JOIN dbo.MainContractor ON
dbo.ContractorPassSystem.M
left outer JOIN
dbo.Contractor_Names ON
dbo.MainContractor.Contrac
WHERE (dbo.ContractorPassSystem.
Select * from ctecontractor where Row BETWEEN 1 and 20
(
SELECT *,
ROW_NUMBER() OVER (ORDER BY LastDateUpdate desc) AS Row
FROM
(
SELECT LastDateUpdate
dbo.Contractor_Names.Contr
dbo.ContractorPassSystem.*
INNER JOIN
dbo.ContractorTypes ON
dbo.ContractorPassSystem.C
left outer JOIN dbo.MainContractor ON
dbo.ContractorPassSystem.M
left outer JOIN
dbo.Contractor_Names ON
dbo.MainContractor.Contrac
WHERE (dbo.ContractorPassSystem.
Select * from ctecontractor where Row BETWEEN 1 and 20
ASKER
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
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
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...