CODEWITHSUNDEEP
db2
user1766002
user1766002

Reputation: 41

DB2 query - selecting specific duplicated records

I am pretty new to dealing with DB2 - on an IBM AS400.

I ran the following query :

SELECT *                                                         
FROM mytable
WHERE ID = 1963003909

This is the results of the query:

     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 in the VRSN column to be able to put the information into a report.

I have tried to read other examples from this site, but couldn't find anything that wasn't showing how to delete duplicate records from the table.

Thanks

Upvotes: 4

Views: 2664

Answers (2)

Matt Stephenson
Matt Stephenson

Reputation: 8620

I don't have DB2 in front of me now, but you should be able to order by vrsn using an OLAP function (ANSI SQL calls them window functions) such as ROW_NUMBER(). Then select based on your ordering. 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

Basically, DB2 will assign number 1 to the row with the highest vrsn for every id, and on and on, and different OLAP functions will break ties in vrsn in different ways.

Upvotes: 2

Benny Hill
Benny Hill

Reputation: 6240

See if this will work for you:

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

You didn't show a library name in your example but I have.

Upvotes: 1

Related Questions