Order by query in datalist by two columns

 
1
0
-1

Hi experts,

I need to sort my list using order by based on two column. My query works when i run in mysql tool but prompt error 'Failed to retrieve column' when run in Joget. I also know that we can set order by column in datalist properties but it only allows sorting by 1 column only.

My query is as below:

SELECT app_fd_candidateData.id, c_req_id, c_name, c_course, c_cgpa, c_department, c_experience, c_resume, c_status FROM app_fd_candidateData
JOIN app_fd_candidateEvent
ON app_fd_candidateEvent.c_eventName = app_fd_candidateData.c_eventName
WHERE (app_fd_candidateData.c_status = 'Interview In Progress' OR app_fd_candidateData.c_status = 'Waiting to be Interview')
AND app_fd_candidateEvent.c_eventStatus = 'Active'
ORDER BY c_status ASC, c_req_id ASC

Is it possible to run this query in Joget datalist?

    CommentAdd your comment...

    2 answers

    1.  
      2
      1
      0

      Hi all, this problem already solved. Just need to use different syntax for Joget v6.

      SELECT app_fd_candidateData.id, c_req_id, c_name, c_course, c_cgpa, c_department, c_experience, c_resume, c_status,
      ROW_NUMBER() OVER (ORDER BY c_status ASC, c_req_id ASC) AS No
      FROM app_fd_candidateData
      JOIN app_fd_candidateEvent
      ON app_fd_candidateEvent.c_eventName = app_fd_candidateData.c_eventName
      WHERE (app_fd_candidateData.c_status = 'Interview In Progress' OR app_fd_candidateData.c_status = 'Waiting to be Interview')
      AND app_fd_candidateEvent.c_eventStatus = 'Active'

        CommentAdd your comment...
      1.  
        1
        0
        -1

        hye.. 

        may i ask, if i want to sort by dateCreated..

        original : select * from app_fd_document where c_division = 'HR' ORDER BY dateCreated ASC.

        ive got a message that said cannot retrieve column..

        Then, what should i change in my query?

          CommentAdd your comment...