Sort multiple columns

Question

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.

Please describe what you are trying to do and tag the question with the database you are using. — Gordon Linoff
– Gordon Linoff, Commented Aug 14, 2015 at 11:47

A Hocevar · Accepted Answer · 2015-08-14 11:48:29Z

2

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

edited Aug 14, 2015 at 11:48

answered Aug 14, 2015 at 11:43

A Hocevar

7363 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sort multiple columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related