• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 887
  • Last Modified:

SELECT SPECIFIC ROWS IN A TABLE

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?
0
Smart222
Asked:
Smart222
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
DB2 ? Oracle ?
when you append the 12 rows, any specific criteria to identify those rows somehow?
0
 
momi_sabagCommented:
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
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
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.

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

Open in new window


HTH,
DaveSlash
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Smart222Author Commented:
This is DB2, not sure of any specific criteria, what does that mean though?
Thanks
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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?
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
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


HTH,
DaveSlash
0
 
Tomas Helgi JohannssonCommented:
Hi!

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

http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0000970.html

(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.

Regards,
    Tomas Helgi
0
 
tliottaCommented:
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.

Tom
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now