CODEWITHSUNDEEP
sqldb2
Steve Dyke
Steve Dyke

Reputation: 175

DB2 SQL Result Set without Duplicates

I am trying to adjust this SQL syntax to only show one row if it has a dash number in the field or field is empty.

Select Distinct TRIM(TRANSLATE(itnbr,' ','F')),
Case When t3.dashonly Is NULL Then '' Else t3.dashonly End As dashonly
From amflib1.itmrva t1
Join webprddt1.drawmext17 t2 On t2.afctdwg = t1.uu25
Left Join webprddt1.wqmssoadn t3 On t3.itemno = t1.itnbr
Where t2.recseq = '0060' Order By 1

As is the resultset is:

 00001           DASHONLY
 --------------- ---------------
 41031052-1      
 41031052-1      -1             
 41031052-10     
 41031052-11     -11            
 41031052-11     
 41031052-12     
 41031052-12     -12            
 41031052-13     
 41031052-14     
 41031052-15     
 41031052-17

Desired resultset:

 00001           DASHONLY
 --------------- ---------------
 41031052-1      -1             
 41031052-10     
 41031052-11     -11            
 41031052-12     -12            
 41031052-13     
 41031052-14     
 41031052-15     
 41031052-17

Upvotes: 0

Views: 48

Answers (1)

The Impaler
The Impaler

Reputation: 48780

Thought I don't have test data to test your query, the query below should work.

You can use ROW_NUMBER() to sort the rows in each subgroup according to a sorting criteria, and then just pick the first one per group.

select *
from (
  select
    TRIM(TRANSLATE(itnbr,' ','F')),
    case when t3.dashonly is null then '' else t3.dashonly end As dashonly,
    row_number() over(partition by TRIM(TRANSLATE(itnbr,' ','F'))
      order by case when t3.dashonly is null then 0 else 1 end) as rn
  from amflib1.itmrva t1
  join webprddt1.drawmext17 t2 on t2.afctdwg = t1.uu25
  left join webprddt1.wqmssoadn t3 on t3.itemno = t1.itnbr
  where t2.recseq = '0060'
) x 
where rn = 1
order by 1

Upvotes: 1

Related Questions