Posted on 2012-08-23
Last Modified: 2012-08-30
Hello Experts

Anybody know how to select a specific row or set of rows from a table.  For instance, I append 12 rows to a table and only want to see those last 12 rows.  Is there a way to do that with SQL?
Question by:Smart222
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    DB2 ? Oracle ?
    when you append the 12 rows, any specific criteria to identify those rows somehow?
    LVL 37

    Expert Comment

    basically you will have to have some way of identifying those rows

    db2 supports select from insert, which means, you can select the rows that were inserted by an insert statement, so if all the 12 rows were inserted using a single statement, that might be the perfect solution for you
    LVL 18

    Accepted Solution

    If the table has a column for something like a "creation timestamp", you can pretty easily use an ORDER BY on that column in descending order. Then, just pick up the first 12 rows.

    select *
      from MyTable
     order by creationTimestamp desc
     fetch first 12 rows only

    Open in new window


    Author Comment

    This is DB2, not sure of any specific criteria, what does that mean though?
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    >not sure of any specific criteria, what does that mean though?

    are there any values in the inserted rows which can help to tell:
    those are my last 12 rows I (or someone else) inserted

    so, the question is :
    * are these rows to be those I (my current session) inserted, or could it be any 12 rows, just the last (in terms of time) ones?
    LVL 18

    Expert Comment

    Something like this might do it for you:

    with numberedRows as (
    select someData,
           row_number() over() as theRow
      from member
    select *
      from numberedRows
     where theRow > (
           select max(theRow)
             from numberedRows) - 12

    Open in new window

    LVL 24

    Expert Comment

    by:Tomas Helgi Johannsson

    What you could do is wrapp your insert statement with an select statement like the example no 6 found in the DB2 manual

    (this feature has been since v8 both on LUW and z/OS if I remmember it correctly)

    Then you immediately see what was successfully inserted by the statement.

        Tomas Helgi
    LVL 26

    Expert Comment

    If the rows do not have a column that identifies them (and you don't capture them at the time of the insert as noted by TomasHelgi), then something like a journal is the only other possibility that might guarantee the set of rows.

    For example, a row might have a column that contains a timestamp that records when it was inserted. You could use such a timestamp value to retrieve rows that were inserted at that time. Or a column might contain something like a "batch number" that was assigned when the rows were inserted. Rows with that batch number could be retrieved.

    But you can't simply think in terms of "the last rows" like these are always added to the end of the table. The physical order of rows might have no relationship to the order they were inserted.

    If you need to know the order in which rows were inserted, you have to do something to track the order.


    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Suggested Solutions

    Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now