0

I am using Angular Material Grid which has Server Side Pagination facility and pulling data from the Oracle Database

In this I want to Sort the data for the whole Grid instead of 1st Page on click of the Header of the Grid.

Internally, I using the below Query to fetch paged records from the Database but it failed in Sorting the whole Data and display first 10 records in the Front-End.

What needs to be changed in the below SQL Query to sort the whole Table data and return first 10 records?

string sqlUsers = " WITH SELECTION AS ( " +
    " SELECT ROW_NUMBER() OVER(ORDER BY A.USERNAME) RN, A.USERID, A.USERNAME " +
    " FROM USER A WHERE A.ROLEID = :roleId) " +
    " SELECT " + pageNumber + " PAGE_NUMBER, CEIL((SELECT COUNT(*) FROM SELECTION ) / " + pageSize + ") TOTAL_PAGES, " + pageSize +
    " PAGE_SIZE, (SELECT COUNT(*) FROM SELECTION ) TOTAL_ROWS,  " +
    " SELECTION.* FROM SELECTION " +
    " WHERE RN BETWEEN((" + pageSize + "*" + pageNumber + ")- " + pageSize + " + 1) AND (" + pageSize + "*" + pageNumber + ")";
if (string.IsNullOrEmpty(sortDirection) || sortDirection == "asc")
{
    sqlUsers = sqlUsers + " ORDER BY USERNAME";
}
else
{
    sqlUsers = sqlUsers + " ORDER BY USERNAME DESC ";
}
CPK_2011
  • 872
  • 3
  • 21
  • 57
  • try with 'OFFSET x ROWS FETCH NEXT n ROWS ONLY' aproach – J.Salas Jan 13 '22 at 14:15
  • You are talking about which page to retrieve. But I want to Sort the whole 1000 records from the Database and display from Z in the first page with 10 set of records. The current Query works perfectly in fetching particular page data. Hope I made it clear. – CPK_2011 Jan 13 '22 at 14:17
  • Please don't ask the same question twice. If the first question got closed erroneously then edit that question to clarify what you are asking and leave a comment to the people who closed it and ask for it to be reopened. – MT0 Jan 13 '22 at 14:27

0 Answers0