CODEWITHSUNDEEP
sqlgroup-bydb2max
Ardia
Ardia

Reputation: 89

SQL Select from a single compound table

I'm new to SQL, so apologies if I got the nomenclature all wrong (or if the solution is blindingly obvious).

My code is something like:

Select Client_id, Max(Year_end_date),  Acct_Nbr    
From  (   
      --  ** subquery ** 
) As AA    
Group By Client_id, Acct_nbr;

The columns in the subquery is the same as the main query. However I get some duplicates in the answer - meaning same for a given Client_id DB2 returns multiple rows with different dates - for example 

Client_id | Year_end_date | Acct_nbr   
-------------------------------------
20001       2003-12-31      01    
20001       2005-12-31      01

Any idea why?

Upvotes: 0

Views: 87

Answers (1)

Hogan
Hogan

Reputation: 70523

Try this:

Select '>>' || Client_id || '<<', Max(Year_end_date), '>>' || Acct_Nbr || '<<'
From  (   
      --  ** subquery ** 
) As AA    
Group By Client_id, Acct_nbr;

You can also try calling TRIM() on the Client_id and Acc_nbr fields in the sub-query. I think you have some hidden spaces there.

Upvotes: 2

Related Questions