SQL Query to Group Columns

Question

I am running a query which I want to only show distinct customers from.

At the current time I am receiving records which have multiple records for example 3 records for Item A0003. I want to only return the last record in the sequence.

My code:

select OJCUNO AS Item,OJPRRF as code,OJFVDT as From Date, OJLVDT as To Date
from M3FDBPRD.OPRICH 
WHERE
   OJCUNO in ( Select max(OJCUNO) FROM OPRICH group by OJCUNO )

Data Sample:

Item        Code    From Date       To Date
A0007       AD      20030301        20161231
A0008       AF      20030301        20161231
A0009       AL      20030301        20121229
A0009       AL      20030301        20121231

Expected Result:

Item        Code    From Date       To Date
A0007       AD      20030301        20161231
A0008       AF      20030301        20161231
A0009       AL      20030301        20121231

It is not clear from your sample what you want to group on, item is unique in all rows and the sql query doesn't match the sample data. Improve your input and add expected output — Joakim Danielson, Dec 03 '18 at 10:44
The data sample you have shown in the question contains the following columns: `Item , Code , From Date , To Date`, while the query has these columns: `OJCUNO AS CUSTOMER,OJPRRF,OJFVDT,OJLVDT `. These two seems to be completely unrelated. Please explain this discrepancy, because your question is unclear. — krokodilko, Dec 03 '18 at 10:45
Possible duplicate of [SQL select only rows with max value on a column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Clockwork-Muse, Dec 03 '18 at 21:26

Jim Jimson · Answer 1 · 2018-12-03T14:06:50.647

0

I'm just having a stab here because I don't have access to a DB2 database to test on, but:

SELECT *
FROM
(
select OJCUNO AS Item,
OJPRRF as code,
OJFVDT as From Date,
OJLVDT as To Date,
ROW_NUMBER() OVER (PARTITION BY OJCUNO, OJPRRF ORDER BY OJFVDT DESC, OJLVDT DESC) AS RNum
from M3FDBPRD.OPRICH 
WHERE
   OJCUNO in ( Select max(OJCUNO) FROM OPRICH group by OJCUNO )
) a
WHERE a.RNum = 1

edited Dec 03 '18 at 14:06

answered Dec 03 '18 at 11:42

Jim Jimson

2,368
3
17
40

Does not like the . after To date. Can you advise? – George H Dec 03 '18 at 14:04

score 0 · Accepted Answer · answered Dec 03 '18 at 12:04

0

Just use row_number():

select OJCUNO AS Item, OJPRRF as code ,OJFVDT as FromDate, OJLVDT as ToDate
from (select o.*,
             row_number() over (partition by ojcuno order OJPRRF desc, OJLVDT desc) as seqnum
      from M3FDBPRD.OPRICH o
     ) o
where seqnum = 1;

Your approach, using a correlated subquery would work if you used the right columns:

select OJCUNO AS Item,OJPRRF as code, OJFVDT as FromDate, OJLVDT as ToDate
from M3FDBPRD.OPRICH 
where OJLVDT in ( Select max(OJLVDT) from OPRICH group by OJCUNO );

answered Dec 03 '18 at 12:04

Gordon Linoff

1,242,037
58
646
786

Your 2-nd query provides wrong results on a slightly different input data (multiple OJLVDT values for some particular OJCUNO are among max OJLVDT for some another OJCUNO). The query should look like below. `with OPRICH (OJCUNO, OJPRR, OJFVDT , OJLVDT) as (values ('A0007', 'AD', 20030301, 20161231) , ('A0008', 'AF', 20030301, 20161231) , ('A0009', 'AL', 20030301, 20161231) , ('A0009', 'AL', 20030301, 20171231) ) select t.* from OPRICH t join (select OJCUNO, max(OJLVDT) OJLVDT from OPRICH group by OJCUNO) g on g.OJCUNO=t.OJCUNO and t.OJLVDT=g.OJLVDT;` – Mark Barinstein Dec 03 '18 at 13:51

SQL Query to Group Columns

2 Answers2