CODEWITHSUNDEEP
sqlsorting
Madhav pandey
Madhav pandey

Reputation: 361

Sort multiple columns

On load I am getting only 20 records(max limit for the page is 20 records) out of 1000 records sorted by "Create_date desc". Now I want to perform sorting on these 1000 records only which should be ordered by SCHEDULE_ID ASC/DESC.

Following is the query :

select * 
  from ( 
    select * 
      from (
        select SCHEDULE_ID, 
               DEPARTMENT, 
               STATUS, 
               CREATE_DATE, 
               rownum ano
          from M_T_NEMO 
        where STATUS like 2 
          AND ROWNUM <= 1000 
        ORDER BY CREATE_DATE DESC
        ) 
      ORDER BY SCHEDULE_ID ASC
      ) 
    where ano >= 0 
      AND ano <= 20

Per page there can be only 20 records. Is this query correct? Right now it is only sorting 20 records per page which is wrong.

Upvotes: 0

Views: 53

Answers (1)

A Hocevar
A Hocevar

Reputation: 726

is there a reason for you to use all these nested SELECT statements ?

You could really rewrite it like

SELECT SCHEDULE_ID, 
  DEPARTMENT, 
  STATUS, 
  CREATE_DATE, 
  ROWNUM ano
FROM M_T_NEMO 
WHERE STATUS = 2 
  AND ROWNUM <= 20 
ORDER BY CREATE_DATE DESC, SCHEDULE_ID ASC

Actually this might not work with Oracle, as the ROWNUM is assigned before sorting

SELECT sub.*
  ROWNUM
FROM (
  SELECT SCHEDULE_ID, 
    DEPARTMENT, 
    STATUS, 
    CREATE_DATE
  FROM M_T_NEMO 
  WHERE STATUS = 2 
  ORDER BY CREATE_DATE DESC, SCHEDULE_ID ASC
  ) sub
WHERE ROWNUM BETWEEN 1 AND 20

This will allow you to page your output by simply incrementing the values for your BETWEEN operator

Upvotes: 2

Related Questions