DB2 query - select specific duplicate records

Question

I'm new to working with DB2 - on the IBM AS400.

I executed the following query:

SELECT * FROM mytable WHERE ID = 1963003909 

These are the query results:

  ID WK END DT VRSN AMT ----------- ---------- ------ ------------ 1963003909 2012-03-24 1 0.00 1963003909 2012-03-31 1 0.00 1963003909 2012-04-07 1 0.00 1963003909 2012-04-14 1 0.00 1963003909 2012-04-21 1 0.00 1963003909 2012-04-28 1 0.00 1963003909 2012-05-05 1 0.00 1963003909 2012-05-12 1 0.00 1963003909 2012-05-19 1 0.00 1963003909 2012-05-26 1 0.00 1963003909 2012-06-02 1 0.00 1963003909 2012-06-09 1 0.00 1963003909 2012-06-16 1 0.00 1963003909 2012-06-23 1 0.00 1963003909 2012-06-30 1 0.00 1963003909 2012-07-07 1 0.00 1963003909 2012-07-14 1 0.00 1963003909 2012-07-21 1 0.00 1963003909 2012-07-28 1 0.00 1963003909 2012-07-28 2 320.00 

I want to be able to modify the query, to select only the records with the maximum value in the VRSN column, in order to be able to put information in the report.

I tried to read other examples from this site, but could not find anything that did not show how to remove duplicate entries from the table.

thanks

Answer 1

I don’t have DB2 right now, but you can order vrsn using the OLAP function (ANSI SQL calls their window functions), for example ROW_NUMBER() . Then choose based on your order. Start with something like this:

 with data(<column_list>, Ranking) as ( select *, ROW_NUMBER() OVER(PARTITION BY id ORDER BY vrsn desc) as Ranking from mytable ) select * from data where Ranking = 1 

In principle, DB2 will assign the number 1 row with the highest vrsn for each id , as well as incl. and on, and various OLAP functions will break connections in vrsn in different ways.

Answer 2

See if this will work for you:

 SELECT * FROM mylibrary.mytable WHERE ID = 1963003909 AND VRSN = (SELECT MAX(VRSN) FROM mylibrary.mytable) 

You did not specify the name of the library in your example, but I have.