CODEWITHSUNDEEP
sqlselectdb2
user2075861
user2075861

Reputation: 117

sql - select record same value for a field, different on another

I have this query:

select CPRCOSC, CPRCDCO, FCHPRDT ,CPRDTPA
from ocsacpr,
     fasaorl,
     fasaorh
where CPRCOSC = 'GHY02'
  and fclcosc = CPRCOSC
  and fclcdcm = CPRCDCO
  and fclverc = CPRVER
  and fclnumc = CPRNUM
  and FCHORDN = FCLORDN
  and FCHCOSC = FCLCOSC

It gives me:

CPRCOSC CPRCDCO     FCHPRDT     CPRDTPA
GHY02   2015000036  20150728    20150728
GHY02   2015000037  20150723    20150723
GHY02   2015000038  20150722    20150722
GHY02   2015000039  20150918    20150930
GHY02   2015000040  20150918    20150930
.....
GHY02   2015000041  20150731    20150731
GHY02   2015000041  20150918    20150831
GHY02   2015000041  20150921    20150930

For the last 3 records (that have same CPRCDCO), I want to select only the record with FCHPRDT higher (20150921 in the example).

How could I edit the query?

Thanks in advance

Upvotes: 1

Views: 124

Answers (2)

jarlh
jarlh

Reputation: 44795

Use a common table expression (to save some typing). Use NOT EXISTS to return a row if there are no other row with same CPRCDCO that has a higher FCHPRDT:

with cte as
(
 select CPRCOSC, CPRCDCO, FCHPRDT ,CPRDTPA
 from ocsacpr,
      fasaorl,
      fasaorh
 where CPRCOSC = 'GHY02'
   and fclcosc = CPRCOSC
   and fclcdcm = CPRCDCO
   and fclverc = CPRVER
   and fclnumc = CPRNUM
   and FCHORDN = FCLORDN
  and FCHCOSC = FCLCOSC
)
select * from cte t1
where not exists (select 1 from cte t2
                  where t2.CPRCDCO = t1.CPRCDCO
                    and t2.FCHPRDT > t1.FCHPRDT)

Upvotes: 0

Leonardo Freitas
Leonardo Freitas

Reputation: 130

You can use window function (FIRST_VALUE), partitioned by CPRCDCO and sorted by FCHPRDT (descending) to do that. Take a look at the function documentation.

https://msdn.microsoft.com/en-us/library/hh213018.aspx

The following code is going to work well, but I suggest you to also use INNER JOIN clause instead of making the join in the WHERE clause.

SELECT DISTINCT
      CPRCOSC, 
      CPRCDCO, 
      FIRST_VALUE(FCHPRDT) OVER (PARTITION BY CPRCDCO ORDER BY FCHPRDT DESC) AS FCHPRDT,
      FIRST_VALUE(CPRDTPA) OVER (PARTITION BY CPRCDCO ORDER BY FCHPRDT DESC) AS CPRDTPA
    FROM 
      OCSACPR, FASAORL, FASAORH
    WHERE 
      CPRCOSC = 'GHY02'
      AND FCLCOSC = CPRCOSC
      AND FCLCDCM = CPRCDCO
      AND FCLVERC = CPRVER
      AND FCLNUMC = CPRNUM
      AND FCHORDN = FCLORDN
      AND FCHCOSC = FCLCOSC

Upvotes: 1

Related Questions