Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2015
    Posts
    8

    Talking Answered: SQL code: How to read database from bottom to top

    Hello Everyone,

    I'm trying to read the last 500 rows of my database using the following sql command:

    SELECT * FROM mytable LIMIT 500 OFFSET (TotalNumberOfRows - 500)

    First of all it takes too much time to process the command(around 5 sec). My database is quite big with 100 000 rows but i have noticed that if i use the same command without "offset" it takes around 20-50 msec.

    Furthermore i would like to read rows from bottom, to display the latest rows. At the moment i swap the data once i have read, but there is way to get rows already from higher index to lower.

    Thanks for help.

  2. Best Answer
    Posted by Pat Phelan

    "
    Quote Originally Posted by Jimmy93 View Post
    I understood that is not the best way to get records by their position in database.
    By definition, rows don't have any position within a database. Think of this as beads (rows) in a bag (table). Every time you reach into the bag, the beads can and probably do change their relative positions. Until you pull the rows out and apply some order to the beads on the table (a result set), there isn't any order.

    You're accustomed to thinking of files and tables resemble files in some ways, but tables are not files.

    -PatP"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use something like:
    Code:
    SELECT *
       FROM myTable
       ORDER BY TheDateColumn DESC
       LIMIT 500;
    Note that having an index on TheDateColumn will help performance a great deal.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Aug 2015
    Posts
    8
    Quote Originally Posted by Pat Phelan View Post
    I'd use something like:
    Code:
    SELECT *
       FROM myTable
       ORDER BY TheDateColumn DESC
       LIMIT 500;
    Note that having an index on TheDateColumn will help performance a great deal.

    -PatP

    Thanks for reply.

    I would like to know if there is a way to read records by their position in database. if i have 100 records in my database and i want to read the record number 98, is that possible through sql command?

    Thanks.

  5. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    be careful : display order is not always correct - it can be db2 is reading with use of index ....
    the best is to access the data by specific key
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  6. #5
    Join Date
    Aug 2015
    Posts
    8
    Quote Originally Posted by przytula_guy View Post
    be careful : display order is not always correct - it can be db2 is reading with use of index ....
    the best is to access the data by specific key
    Thanks.

    I understood that is not the best way to get records by their position in database.
    But if i want to do for particular circumstances is it possible to do so?

  7. #6
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    The concept of "position" inside a relational database does not have the meaning that you seem to want to give it.
    What do you mean by "the last 500 rows"?
    Do you mean "the most recently inserted according to timestamp of insert"?
    Do you mean "the rows with the highest distinct 500 values in some column"?
    Or something else?
    Think.
    Think again.

  8. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Jimmy93 View Post
    I understood that is not the best way to get records by their position in database.
    By definition, rows don't have any position within a database. Think of this as beads (rows) in a bag (table). Every time you reach into the bag, the beads can and probably do change their relative positions. Until you pull the rows out and apply some order to the beads on the table (a result set), there isn't any order.

    You're accustomed to thinking of files and tables resemble files in some ways, but tables are not files.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #8
    Join Date
    Aug 2015
    Posts
    8
    Quote Originally Posted by Pat Phelan View Post
    By definition, rows don't have any position within a database. Think of this as beads (rows) in a bag (table). Every time you reach into the bag, the beads can and probably do change their relative positions. Until you pull the rows out and apply some order to the beads on the table (a result set), there isn't any order.

    You're accustomed to thinking of files and tables resemble files in some ways, but tables are not files.

    -PatP
    I have been working for years with codebase(xbase) and moving to Db2 is a big step.
    Thanks for your explanation it was useful.

Tags for this Thread

Posting Permissions

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