Professional Web Applications Themes

How can i retrieve record ONLY From 300 - 400 in amillion Record Table? - Coldfusion Database Access

Removed by Administrator...

  1. Moderated Post

    Default How can i retrieve record ONLY From 300 - 400 in amillion Record Table?

    Removed by Administrator
    keonglah Guest
    Moderated Post

  2. #2

    Default Re: How can i retrieve record ONLY From 300 - 400 in amillion Record Table?

    Hi there

    You can try this - it seems to work

    SELECT *
    FROM tableX
    WHERE rowid in (
    SELECT rowid FROM tableX
    WHERE rownum <= 400
    MINUS
    SELECT rowid FROM tableX
    WHERE rownum < 300);

    Thius will retrieve all records with a row number between 300 & 400

    Hope this helps


    toby007 Guest

  3. #3

    Default Re: How can i retrieve record ONLY From 300 - 400 in amillion Record Table?

    Why not nto use simple
    SELECT *
    FROM tableX
    WHERE rownum between 300 and 400?
    CF_Oracle Guest

  4. #4

    Default Re: How can i retrieve record ONLY From 300 - 400 in amillion Record Table?

    SELECT *
    FROM tableX
    WHERE rownum between 300 and 400?

    Did you try it? Because it returns no rows. Oracle begins the calculation of rownum at 1 for any recordset it returns.

    JR


    jonwrob Guest

  5. #5

    Default Re: How can i retrieve record ONLY From 300 - 400 in amillion Record Table?

    JR, you are right. I used not autonumber but regular number coulmn format.
    CF_Oracle Guest

  6. #6

    Default Re: How can i retrieve record ONLY From 300 - 400 in amillion Record Table?

    If this is for paging.

    Try this:
    SELECT TOP 100 * FROM tablex
    for the first set then record the last record ID. Then for the second,
    third... pages
    SELECT TOP 100 * FROM tablex WHERE ID > #URL.lastID#



    krisbrixon Guest

  7. #7

    Default Re: How can i retrieve record ONLY From 300 - 400 in amillion Record Table?

    HI.. Thanks for all reply..

    Maybe i try to rewrite my question..~

    Im have a table , for example, UserMaster hold million Records. I would like
    to generate a page where is only show 100 Records and there is a next and
    previous button allow user move to the next or pevious page. IF just used a
    [select fiend Name from UserMaster] it will store million of record in a memory
    but i just want to used only 100 Record per page. Inorder to avoid this, how
    can i retrieved ONLY 100 active record per page by using query, store pro or
    view?

    Senario
    The table have field call UserID and auto added by one.
    Some record maybe deleted.
    eg.. The ID in the table will be like.. 300,301,302,305,306..400 (303 and 304
    record is deleted.) Inorder to retrieved 100 record per page.. the ID is start
    from 300 till 402.


    To krisbrixon :
    >If this is for paging.
    >Try this:
    >SELECT TOP 100 * FROM tablex
    >for the first set then record the last record ID. Then for the second,
    third... pages
    >SELECT TOP 100 * FROM tablex WHERE ID > #URL.lastID#
    This is a pretty good idea.. but i guess we have to change url.lastID to
    session.lastID to avoid user manually change the ID.

    How bout using the following query?
    SELECT TOP 100 * FROM tablex
    WHERE ID beteween [StartID] add [EndID]

    Question.. How can i get the StartID and EndID value??

    keonglah Guest

Similar Threads

  1. How to select record on one table but not the other
    By Rustywater in forum Coldfusion Database Access
    Replies: 1
    Last Post: September 11th, 11:15 AM
  2. Insert Record and Retrieve autonumber extension
    By galeemma in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 2
    Last Post: April 21st, 05:14 PM
  3. Record Set table layout?
    By Jefferis NoSpamme in forum Macromedia Dynamic HTML
    Replies: 5
    Last Post: February 6th, 10:37 PM
  4. post record to table
    By LGarcia in forum Microsoft Access
    Replies: 3
    Last Post: August 1st, 06:15 PM
  5. Stop adding record in subform after record count = 1
    By Charlie in forum Microsoft Access
    Replies: 0
    Last Post: July 31st, 10:42 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139