• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • 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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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