By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,585 Members | 2,235 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,585 IT Pros & Developers. It's quick & easy.

Select the latest record for each ID from a table of historic records

P: n/a
Dear all,

I am now designing a table for storing inventory of servers in a DB2
DB. However, I am new to SQL and would like to seek advice whether the
design could work as expect with DB2 SQL.

The high level of initial design is as follows:

SNAME VARCHAR(30), # Server Name
VAL INTEGER, # Server Value
EDATE DATE, # Effective date of the record

For example
SNAME VAL EDATE
----- ------ -----------
A 2 2005-10-1
A 3 2005-10-2
B 4 2005-10-8
B 5 2005-10-9

Could anyone suggest a SQL to extract only the latest record of server
A and B? In addition, any suggestions on the design of table with
historical
records??

Thank a lots!!

Henry

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
I would think something like this may work (did not try running the
code, could have syntax mixed up a bit)...

select sname,val,edate from table where sname,edate in (
select sname,max(edate) from table
group by sname)

i would also consider maybe adding an active_flg field. then if your
application would set it when inserting the new records (and
deactivating older records of course). then you could just add that
field to your where clause which would speed up the selects

Nov 12 '05 #2

P: n/a
If you are on DB2 V8.1 and your table is called say INVENTORY:

SELECT sname, val, edate FROM (
SELECT sname, val, edate,
ROW_NUMBER() OVER( PARTITION BY sname ORDER BYedate DESC )
rn
FROM inventory WHERE sname IN ('A','B')
) x
WHERE rn = 1

There also the unique constraint by (SNAME, EDATE, VAL) seems to be
needed on that table at least the query above to work unambiguousely.

Good luck!
-Eugene

Nov 12 '05 #3

P: n/a
Thank you very much for all the valuable advance!

Henry
- 2 Nov 05

Nov 12 '05 #4

P: n/a
Henry wrote:
Dear all,

I am now designing a table for storing inventory of servers in a DB2
DB. However, I am new to SQL and would like to seek advice whether the
design could work as expect with DB2 SQL.

The high level of initial design is as follows:

SNAME VARCHAR(30), # Server Name
VAL INTEGER, # Server Value
EDATE DATE, # Effective date of the record

For example
SNAME VAL EDATE
----- ------ -----------
A 2 2005-10-1
A 3 2005-10-2
B 4 2005-10-8
B 5 2005-10-9

Could anyone suggest a SQL to extract only the latest record of server
A and B? In addition, any suggestions on the design of table with
historical
records??


Try (assumming sname alone is the primary key):

select sname, val, edate
from this_table as A
where edate = (
select max(edate)
from this_table as B
where B.sname = A.sname
);
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.