• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

The order by does not work properly when I add the JOIN statements

I tested the same query with simple select and it works fine.  I am really disappointed and confuse why this pagination has to be so difficult.

please help!!

WITH    PagingCTE AS
 ( SELECT  ID, Date,AceData.Description as AceData_Description, Job,type,InCredit, OutDebit , ROW_NUMBER() OVER ( ORDER BY Type aSC) ROW FROM  ListType  
JOIN AceData ON ListTYPE.TypeList = AceData.Type  JOIN ListJob ON ListJob.JobList = AceData.Job  
 ) SELECT ID, Date,AceData_Description, Job,type,InCredit, OutDebit FROM    PagingCTE
0
goodk
Asked:
goodk
4 Solutions
 
goodkAuthor Commented:
I am using MS SQL 8 Express edition
0
 
SharathData EngineerCommented:
Can you post the result of your query and the expected result.
0
 
GhunaimaCommented:
You did'nt specify any order by clause in your statement. Add it & then run
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
goodkAuthor Commented:
Ghunaima:  Please look - I do have the order by clause

Can you guys try the same in your SM studio?  My question is quite straight forward!!

It looks like there is a problem with the way paging works when we add JOIN statements.

kindly, try and help!! thanks
0
 
goodkAuthor Commented:
All I am saying is that ( ORDER BY Type aSC)  does not work when I add the following,
JOIN AceData ON ListTYPE.TypeList = AceData.Type  JOIN ListJob ON ListJob.JobList = AceData.Job  

and it works with out the join statement!!
0
 
AkenathonCommented:
You do NOT have an ORDER BY in your query. the "( ORDER BY Type aSC)" ONLY applies to "ROW_NUMBER() OVER ". You have to add it as follows, else it will never work:

WITH    PagingCTE AS
 ( SELECT  ID, Date,AceData.Description as AceData_Description, Job,type,InCredit, OutDebit , ROW_NUMBER() OVER ( ORDER BY Type aSC) ROW FROM  ListType  
JOIN AceData ON ListTYPE.TypeList = AceData.Type  JOIN ListJob ON ListJob.JobList = AceData.Job  
 ) SELECT ID, Date,AceData_Description, Job,type,InCredit, OutDebit FROM    PagingCTE
ORDER BY Type aSC
0
 
goodkAuthor Commented:

Where can I read up on this pagination- I mean I am totally confused -
where can I find the official documentation, manual etc.. - please help!! thanks

0
 
goodkAuthor Commented:
do not see the info on pagination here,
http://www.sql-tutorial.net

or here,
http://www.w3schools.com/SQl/default.asp


Is there any online documentaion? thanks

0
 
goodkAuthor Commented:
http://softscenario.blogspot.com/2007/11/sql-2005-server-side-paging-using-cte.html

I also saw the above example, but he is talking about a Stored procedure and a pre determined select which he has programmed into the stored procedure.

In my case the the choice is of fields is by the user on the page so, it cannot be pre-determinded.

0
 
GhunaimaCommented:
Pass that field as a parameter to the stored procedure to mak it dynamic
0
 
goodkAuthor Commented:
"common table expressions paging"  that is all I had to google and there was a ton of information and it answered every possible question

0
 
goodkAuthor Commented:
no good references or examples were provided by the experts
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now