How to modify this query to use between startindex and endindex rownum

Question

How to modify this query so that I get the results between rownum 10 and 21 for example:

   SELECT DAL_ROWNOTABLE.DAL_ID
     FROM (SELECT ticket.id AS "DAL_ID" 
          FROM ticket_table ticket 
          WHERE (ticket.type = N'I' ) AND 
        (ticket.tenant IS NULL OR
         ticket.tenant IN (SELECT tgm.tenant_id 
                           FROM tenant_group_member tgm
                           WHERE tgm.tenant_group = HEXTORAW('30B0716FEB5F4E4BB82A7B7AA3A1A42C') 
                          ) 
        )
  ORDER BY ticket.id
 ) DAL_ROWNOTABLE 
  WHERE rownum <= 21;

Any help will be appreciated.

score 0 · Accepted Answer · edited Apr 08 '15 at 09:57

0

Change rownum to row_number():

SELECT DAL_ROWNOTABLE.DAL_ID
FROM (SELECT ticket.id AS "DAL_ID",row_number() over (order by ticket_id) as seqnum
      FROM ticket_table ticket 
      WHERE (ticket.type = N'I' )AND
            (ticket.tenant IS NULL OR
            ticket.tenant IN(SELECT tgm.tenant_id 
                              FROM tenant_group_member tgm
                              WHERE tgm.tenant_group = HEXTORAW('30B0716FEB5F4E4BB82A7B7AA3A1A42C') 
                             ) 
        )
     ) DAL_ROWNOTABLE 
WHERE seqnum between 10 and 21;

edited Apr 08 '15 at 09:57

Evadne Wu

3,190
1
25
25

answered Mar 23 '14 at 11:40

Gordon Linoff

1,242,037
58
646
786

Hmmm, I suggested using ROW_NUMBER() in my answer to this guy's other question. – APC Mar 23 '14 at 13:30
@APC . . . I remember, and that was a fine solution. I used `rownum` just as part of simplifying the query. – Gordon Linoff Mar 23 '14 at 13:33
hahaha Gordon again ...Thanks man will try this out...I thought of using MINUS – garuda Mar 23 '14 at 14:53
I will have to check why the code uses ROWNUM own implementation instead of using ROW_NUMBER which seems to be straightforward according to you... – garuda Mar 23 '14 at 15:13

How to modify this query to use between startindex and endindex rownum

1 Answers1