How to find most recent rows? 
Author Message
 How to find most recent rows?

Hi,

First of all, I thank those who have been very helpful in the past.
How to find most recent rows, say, 10, (for insert and update
transaction), generically?

For insert:
Well, one can find PK column of a given table, suppose, single column,
then determine data type of that column, if it is of integer or
similar type data,
1(a) how would one know that it uses "identity" attribute for it
(thus, incremental)?
1(b) and if it does not, how to proceed?  
2, what if it is of random alphnumeric?
3, what if it is of composite keys?

For update:
???

* Question category 1 is most important to me for now.

Thanks.

DL



Wed, 04 Aug 2004 01:29:23 GMT
 How to find most recent rows?

DL,

There is no way to generically find the most recent rows - especially if
you're looking at updated rows.

The best you could do is to add a TIMESTAMP column to each table with
INSERT and UPDATE triggers to place the current timestamp in the column
for each record.

Quote:

> Hi,

> First of all, I thank those who have been very helpful in the past.
> How to find most recent rows, say, 10, (for insert and update
> transaction), generically?

> For insert:
> Well, one can find PK column of a given table, suppose, single column,
> then determine data type of that column, if it is of integer or
> similar type data,
> 1(a) how would one know that it uses "identity" attribute for it
> (thus, incremental)?
> 1(b) and if it does not, how to proceed?
> 2, what if it is of random alphnumeric?
> 3, what if it is of composite keys?

> For update:
> ???

> * Question category 1 is most important to me for now.

> Thanks.

> DL

--
====================================
To reply, delete the 'x' from my email

Jerry Stuckle
JDS Computer Training Corp.

====================================



Wed, 04 Aug 2004 03:09:08 GMT
 How to find most recent rows?
Jerry,

Thanks for the response.  I have good reasons for the query, for one
thing, say, I'm not owner of a given database, only being given select
permission.
Can you answer part of the question below?  Thanks.  DL

Quote:
> For insert:
> Well, one can find PK column of a given table, suppose, single column,
> then determine data type of that column, if it is of integer or
> similar type data,
> 1(a) how would one know that it uses "identity" attribute for it
> (thus, incremental)?

> DL,

> There is no way to generically find the most recent rows - especially if
> you're looking at updated rows.

> The best you could do is to add a TIMESTAMP column to each table with
> INSERT and UPDATE triggers to place the current timestamp in the column
> for each record.


> > Hi,

> > First of all, I thank those who have been very helpful in the past.
> > How to find most recent rows, say, 10, (for insert and update
> > transaction), generically?

> > For insert:
> > Well, one can find PK column of a given table, suppose, single column,
> > then determine data type of that column, if it is of integer or
> > similar type data,
> > 1(a) how would one know that it uses "identity" attribute for it
> > (thus, incremental)?
> > 1(b) and if it does not, how to proceed?
> > 2, what if it is of random alphnumeric?
> > 3, what if it is of composite keys?

> > For update:
> > ???

> > * Question category 1 is most important to me for now.

> > Thanks.

> > DL



Wed, 04 Aug 2004 07:24:49 GMT
 How to find most recent rows?
I don't think you understood Jerry's reply so let me try again: there is NO
WAY to determine the age of a record by looking at the data on the record,
examining its datatype, etc. etc.

The only exception is if the date that the record was created or changed is
IN the record itself, usually in a column by itself.

Since this is a reasonably common requirement, table designers often have a
column on a table which contains the timestamp representing when the row was
created or updated.

For example, given a table FOO that contains a small integer and a character
field, if you needed to know when each row was added or changed, you could
add a column that had the datatype TIMESTAMP.

--create the table (executed some time in the past)
create table FOO
(id smallint not null,
 name char(20) not null,
primary key(id));

--add a new column to the table (today)
alter table FOO
add update_timestamp timestamp;

The ALTER TABLE will add the UPDATE_TIMESTAMP column to every row that is
currently on the table and initialize its value to null on each of those
rows. You may want to change the value of this column to some arbitrary
value like the current timestamp just so that they have a meaningful value.
You could do this via:
update FOO set update_timestamp = current timestamp;

Or, if you wanted it to equal noon on January 1 2002, the UPDATE statement
would be:
update FOO set update_timestamp = '2002-01-01-12.00.00.000000';

As Jerry pointed out, you could use a trigger to set the value of the
UPDATE_TIMESTAMP column equal to the current timestamp any time you insert a
new row. This approach is excellent because it will work for ANY row added
to the table via ANY application and the user won't have to do anything
except put values in the ID and NAME columns. The insert trigger would look
something like this:
create trigger insert_foo
after insert on foo
referencing new as n
for each row
mode db2sql
update foo set update_timestamp = current timestamp
where id = n.id;

You can also use a trigger to change the value of the UPDATE_TIMESTAMP
column on existing rows whenever anyone changes any value in the row. The
trigger should resemble this example:
create trigger foo_update
after update on foo
referencing new as n old as o
for each row
mode db2sql
update foo set update_timestamp = current timestamp
where id = n.id;

Whenever you need to find the most recent rows, the simplest method is to
simply list the rows of the table sorted on the UPDATE_TIMESTAMP column in
descending order, as in this query:
select * from FOO
order by UPDATE_TIMESTAMP DESC;

By the way, there is no standard way of getting only "n" rows of a
particular set, such as the 10 or 20 most recent rows in a table. SQL is
designed to return *ALL* of the rows in a set that satisfy a query.
Unless you want to write an application which reads the rows in newest to
oldest order one at a time and stops reading when it has hit the desired
count, you will pretty much have to accept all of the rows that satisfy the
query, even if that is many more than you actually want to see.

Rhino


Quote:
> Jerry,

> Thanks for the response.  I have good reasons for the query, for one
> thing, say, I'm not owner of a given database, only being given select
> permission.
> Can you answer part of the question below?  Thanks.  DL
> > For insert:
> > Well, one can find PK column of a given table, suppose, single column,
> > then determine data type of that column, if it is of integer or
> > similar type data,
> > 1(a) how would one know that it uses "identity" attribute for it
> > (thus, incremental)?




Quote:
> > DL,

> > There is no way to generically find the most recent rows - especially if
> > you're looking at updated rows.

> > The best you could do is to add a TIMESTAMP column to each table with
> > INSERT and UPDATE triggers to place the current timestamp in the column
> > for each record.


> > > Hi,

> > > First of all, I thank those who have been very helpful in the past.
> > > How to find most recent rows, say, 10, (for insert and update
> > > transaction), generically?

> > > For insert:
> > > Well, one can find PK column of a given table, suppose, single column,
> > > then determine data type of that column, if it is of integer or
> > > similar type data,
> > > 1(a) how would one know that it uses "identity" attribute for it
> > > (thus, incremental)?
> > > 1(b) and if it does not, how to proceed?
> > > 2, what if it is of random alphnumeric?
> > > 3, what if it is of composite keys?

> > > For update:
> > > ???

> > > * Question category 1 is most important to me for now.

> > > Thanks.

> > > DL



Thu, 05 Aug 2004 10:10:33 GMT
 How to find most recent rows?
I certainly appreciate the responses, however, with due respect, I
have a few comments below.

1) Let me say, I absolutely agree with having columns like
"recordEntryDate (of DateTime type)" and "recordUpdateDate (of the
same or similar type)" for the given requirement.

2) However, I specifically stated, this user DOES NOT have DDL
privilege, having limited DML privilege, that is, "SELECT", and the
information he has about a given table is "owner" and
"tablename(tabname)".  Thus, to meet with the requirement is extremely
challenging if there is an iota possibility.

3) I've been able to list records (most recent first) if the table is
constructed in certain manner, however, for Updated rows, yes, it
seems NO WAY.

4) When you say, "By the way, there is no standard way of getting only
"n" rows of a particular set", are you suggesting the FETCH clause IS
NOT STANDDARD for T-SQL but it works for IBM DB2? for instance,

select *
from JohnDoe.SomeTBL
where col1 = 'thisCondition'
Fetch First 10 Row Only

Regards,

DL

Quote:

> I don't think you understood Jerry's reply so let me try again: there is NO
> WAY to determine the age of a record by looking at the data on the record,
> examining its datatype, etc. etc.

> The only exception is if the date that the record was created or changed is
> IN the record itself, usually in a column by itself.

> Since this is a reasonably common requirement, table designers often have a
> column on a table which contains the timestamp representing when the row was
> created or updated.

> For example, given a table FOO that contains a small integer and a character
> field, if you needed to know when each row was added or changed, you could
> add a column that had the datatype TIMESTAMP.

> --create the table (executed some time in the past)
> create table FOO
> (id smallint not null,
>  name char(20) not null,
> primary key(id));

> --add a new column to the table (today)
> alter table FOO
> add update_timestamp timestamp;

> The ALTER TABLE will add the UPDATE_TIMESTAMP column to every row that is
> currently on the table and initialize its value to null on each of those
> rows. You may want to change the value of this column to some arbitrary
> value like the current timestamp just so that they have a meaningful value.
> You could do this via:
> update FOO set update_timestamp = current timestamp;

> Or, if you wanted it to equal noon on January 1 2002, the UPDATE statement
> would be:
> update FOO set update_timestamp = '2002-01-01-12.00.00.000000';

> As Jerry pointed out, you could use a trigger to set the value of the
> UPDATE_TIMESTAMP column equal to the current timestamp any time you insert a
> new row. This approach is excellent because it will work for ANY row added
> to the table via ANY application and the user won't have to do anything
> except put values in the ID and NAME columns. The insert trigger would look
> something like this:
> create trigger insert_foo
> after insert on foo
> referencing new as n
> for each row
> mode db2sql
> update foo set update_timestamp = current timestamp
> where id = n.id;

> You can also use a trigger to change the value of the UPDATE_TIMESTAMP
> column on existing rows whenever anyone changes any value in the row. The
> trigger should resemble this example:
> create trigger foo_update
> after update on foo
> referencing new as n old as o
> for each row
> mode db2sql
> update foo set update_timestamp = current timestamp
> where id = n.id;

> Whenever you need to find the most recent rows, the simplest method is to
> simply list the rows of the table sorted on the UPDATE_TIMESTAMP column in
> descending order, as in this query:
> select * from FOO
> order by UPDATE_TIMESTAMP DESC;

> By the way, there is no standard way of getting only "n" rows of a
> particular set, such as the 10 or 20 most recent rows in a table. SQL is
> designed to return *ALL* of the rows in a set that satisfy a query.
> Unless you want to write an application which reads the rows in newest to
> oldest order one at a time and stops reading when it has hit the desired
> count, you will pretty much have to accept all of the rows that satisfy the
> query, even if that is many more than you actually want to see.

> Rhino



> > Jerry,

> > Thanks for the response.  I have good reasons for the query, for one
> > thing, say, I'm not owner of a given database, only being given select
> > permission.
> > Can you answer part of the question below?  Thanks.  DL
> > > For insert:
> > > Well, one can find PK column of a given table, suppose, single column,
> > > then determine data type of that column, if it is of integer or
> > > similar type data,
> > > 1(a) how would one know that it uses "identity" attribute for it
> > > (thus, incremental)?



> > > DL,

> > > There is no way to generically find the most recent rows - especially if
> > > you're looking at updated rows.

> > > The best you could do is to add a TIMESTAMP column to each table with
> > > INSERT and UPDATE triggers to place the current timestamp in the column
> > > for each record.


> > > > Hi,

> > > > First of all, I thank those who have been very helpful in the past.
> > > > How to find most recent rows, say, 10, (for insert and update
> > > > transaction), generically?

> > > > For insert:
> > > > Well, one can find PK column of a given table, suppose, single column,
> > > > then determine data type of that column, if it is of integer or
> > > > similar type data,
> > > > 1(a) how would one know that it uses "identity" attribute for it
> > > > (thus, incremental)?
> > > > 1(b) and if it does not, how to proceed?
> > > > 2, what if it is of random alphnumeric?
> > > > 3, what if it is of composite keys?

> > > > For update:
> > > > ???

> > > > * Question category 1 is most important to me for now.

> > > > Thanks.

> > > > DL



Fri, 06 Aug 2004 13:47:03 GMT
 How to find most recent rows?
DL,

Again - it is NOT possible given the conditions you laid out. There is
NO possibility.  You CANNOT do it.

You can't even guarantee it for inserted rows (rows may not be inserted
at the end of the table, for instance), unless you have an IDENTITY or
similar column.
Otherwise, ou can get n rows from a table - but you can't guarantee
these are the first or last rows.

Remember - rows in a SQL database are by definition unordered.  You can
order the rows - but only by data existing within the rows (like a
modification timestamp).

So - there is NO WAY to do this - standard or non-standard - and
guarantee results.

Quote:

> I certainly appreciate the responses, however, with due respect, I
> have a few comments below.

> 1) Let me say, I absolutely agree with having columns like
> "recordEntryDate (of DateTime type)" and "recordUpdateDate (of the
> same or similar type)" for the given requirement.

> 2) However, I specifically stated, this user DOES NOT have DDL
> privilege, having limited DML privilege, that is, "SELECT", and the
> information he has about a given table is "owner" and
> "tablename(tabname)".  Thus, to meet with the requirement is extremely
> challenging if there is an iota possibility.

> 3) I've been able to list records (most recent first) if the table is
> constructed in certain manner, however, for Updated rows, yes, it
> seems NO WAY.

> 4) When you say, "By the way, there is no standard way of getting only
> "n" rows of a particular set", are you suggesting the FETCH clause IS
> NOT STANDDARD for T-SQL but it works for IBM DB2? for instance,

> select *
> from JohnDoe.SomeTBL
> where col1 = 'thisCondition'
> Fetch First 10 Row Only

> Regards,

> DL

--
====================================
To reply, delete the 'x' from my email

Jerry Stuckle
JDS Computer Training Corp.

====================================



Sat, 07 Aug 2004 01:31:54 GMT
 How to find most recent rows?
Jerry,

My statement of "if the table is constructed in certain manner" and
yours
"unless you have an IDENTITY or similar column" is the exception, that
is what I meant.

DL

Quote:

> DL,

> Again - it is NOT possible given the conditions you laid out. There is
> NO possibility.  You CANNOT do it.

> You can't even guarantee it for inserted rows (rows may not be inserted
> at the end of the table, for instance), unless you have an IDENTITY or
> similar column.
> Otherwise, ou can get n rows from a table - but you can't guarantee
> these are the first or last rows.

> Remember - rows in a SQL database are by definition unordered.  You can
> order the rows - but only by data existing within the rows (like a
> modification timestamp).

> So - there is NO WAY to do this - standard or non-standard - and
> guarantee results.


> > I certainly appreciate the responses, however, with due respect, I
> > have a few comments below.

> > 1) Let me say, I absolutely agree with having columns like
> > "recordEntryDate (of DateTime type)" and "recordUpdateDate (of the
> > same or similar type)" for the given requirement.

> > 2) However, I specifically stated, this user DOES NOT have DDL
> > privilege, having limited DML privilege, that is, "SELECT", and the
> > information he has about a given table is "owner" and
> > "tablename(tabname)".  Thus, to meet with the requirement is extremely
> > challenging if there is an iota possibility.

> > 3) I've been able to list records (most recent first) if the table is
> > constructed in certain manner, however, for Updated rows, yes, it
> > seems NO WAY.

> > 4) When you say, "By the way, there is no standard way of getting only
> > "n" rows of a particular set", are you suggesting the FETCH clause IS
> > NOT STANDDARD for T-SQL but it works for IBM DB2? for instance,

> > select *
> > from JohnDoe.SomeTBL
> > where col1 = 'thisCondition'
> > Fetch First 10 Row Only

> > Regards,

> > DL



Sat, 07 Aug 2004 06:54:09 GMT
 How to find most recent rows?
DL,

Can you shed soem light on the big picture problem?
In my experience often we serach for solutions for patches then to the real problem....

Cheers
Serge
--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Canada



Sat, 07 Aug 2004 08:02:40 GMT
 How to find most recent rows?

    Jerry> DL, Again - it is NOT possible given the conditions you
    Jerry> laid out. There is NO possibility.  You CANNOT do it.

I've heard something about append-only tables. I no longer have the
SQL Reference handy to check :-) Maybe somebody can look that up ?

If such things do exist, and I think they perhaps do, then it may be
possible to have a cursor that does a "SELECT *" on the table .. I
think it will read the records in order inserted.

(I am probably talking through my hat, but I remember vaguely
discussing this with some folks once)

--
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh



Sun, 08 Aug 2004 07:36:02 GMT
 How to find most recent rows?
In DB2UDB there's a parameter one can specify at create/alter table.
APPEND ON
This forces inserts to be done at the end of the teble in its free space
instad of first available free space.
But then, when you select, there's no way to force it to select for==rom
there (unless you're using identity as mentionned before).
Again, the only other way would be to request oreder by and fetch first
x rows to give you what you want or to specify limits (and/or range).
Regards, Pierre.
Quote:


>     Jerry> DL, Again - it is NOT possible given the conditions you
>     Jerry> laid out. There is NO possibility.  You CANNOT do it.

> I've heard something about append-only tables. I no longer have the
> SQL Reference handy to check :-) Maybe somebody can look that up ?

> If such things do exist, and I think they perhaps do, then it may be
> possible to have a cursor that does a "SELECT *" on the table .. I
> think it will read the records in order inserted.

> (I am probably talking through my hat, but I remember vaguely
> discussing this with some folks once)



Sun, 08 Aug 2004 06:52:19 GMT
 How to find most recent rows?
Hi all,

I appreciate all the inputs here.  And I was delighted that Serge
posted a philosophical note which I shared, then why did I posted the
initial question?
Well, don't you agree time and again we run into contradiction because
there's no other option?

Regards,

DL

Quote:

> In DB2UDB there's a parameter one can specify at create/alter table.
> APPEND ON
> This forces inserts to be done at the end of the teble in its free space
> instad of first available free space.
> But then, when you select, there's no way to force it to select for==rom
> there (unless you're using identity as mentionned before).
> Again, the only other way would be to request oreder by and fetch first
> x rows to give you what you want or to specify limits (and/or range).
> Regards, Pierre.



> >     Jerry> DL, Again - it is NOT possible given the conditions you
> >     Jerry> laid out. There is NO possibility.  You CANNOT do it.

> > I've heard something about append-only tables. I no longer have the
> > SQL Reference handy to check :-) Maybe somebody can look that up ?

> > If such things do exist, and I think they perhaps do, then it may be
> > possible to have a cursor that does a "SELECT *" on the table .. I
> > think it will read the records in order inserted.

> > (I am probably talking through my hat, but I remember vaguely
> > discussing this with some folks once)



Sun, 08 Aug 2004 11:57:28 GMT
 How to find most recent rows?
I think it's time for some outside-of-the-box thinking.
So my philosophical note meant:
Share you business problem and we may just be able to help you find teh outside-the-box solution.

Cheers
Serge
--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Canada



Sun, 08 Aug 2004 23:24:29 GMT
 How to find most recent rows?
Thanks for the offer, but let me sort out a few things before I post
business problems.

Cheers,

DL

Quote:

> I think it's time for some outside-of-the-box thinking.
> So my philosophical note meant:
> Share you business problem and we may just be able to help you find teh outside-the-box solution.

> Cheers
> Serge



Tue, 10 Aug 2004 13:37:57 GMT
 
 [ 13 post ] 

 Relevant Pages 

1. Query to return most recent row

2. Finding the most recent record in a group

3. Finding most recent record...

4. find most recent date value

5. Select most recent row per company

6. Most recent rows ?

7. find most recent full backup file name

8. Finding most recent record

9. SQL to find most recent record?

10. finding the most recent date and y2k

11. recent inserted rows ....

12. Row with most recent date?


 
Powered by phpBB® Forum Software