0

I am following the below sample to implement Server Side Pagination in Angular

https://blog.angular-university.io/angular-material-data-table/

I have made some changes to the above sample and pulling data from the Oracle Database

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

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
  • 2
    Do not include parameters in SQL statements by using string concatenation; that is how you introduce SQL injection vulnerabilities. Use parameterized queries. – MT0 Jan 13 '22 at 13:45
  • From Oracle 12c, you can use `OFFSET (:pgnum - 1)*:pgsize FETCH NEXT :pgsize ROWS ONLY` and do not need to use `ROWNUM` or `ROW_NUMBER()`. – MT0 Jan 13 '22 at 13:54
  • @MindSwipe. Sorry, It's my mistake to tell that I have got the Answer. But the actual thing is not achieved. I am using Pagination BUT I want to Sort the whole Table Data instead of pulling a particular set of records. If first page contains users starting with A, second page contains users starting with B and so on, then it should combine all the data and sort on the whole Data and display as paginated records as the Title of the Question clearly says. :) – CPK_2011 Jan 13 '22 at 13:57
  • Are you just after [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_21&fiddle=029401efb0c19194798594ab6de5e06d)? Apart from using the analytic count function, that's all covered in the linked duplicate. – MT0 Jan 13 '22 at 14:16

0 Answers0