user6888062 user6888062 - 26 days ago 7
SQL Question

How to do a complex MAX() and GROUP BY in Oracle SQL?

I am concerned about 2 tables

AUF_KOPF
is order header and
AUF_POS
is order items.

I want to get the maximum last date that a product was used on an order item within an order. The product ID is stored in
AUF_POS.GLAS1
,
AUF_POS.GLAS2
or
AUF_POS.GLAS3
.

I have created this query which only checks
AUF_POS.GLAS1
:

Select Max(AUF_KOPF.ERFASS_DAT),
AUF_POS.GLAS1
From AUF_KOPF AUF_KOPF
Inner Join AUF_POS AUF_POS On AUF_KOPF.AUF_NR = AUF_POS.AUF_NR
Group By AUF_POS.GLAS1
Order By AUF_POS.GLAS1


Results:

MAX(AUF_KOPF.ERFASS_DAT) GLAS1
07/11/2016 1
06/11/2016 2
03/11/2016 3
09/11/2016 4
07/11/2016 5


Problem is that product ID
1
may have been used more recently than
07/11/2016
at another position in an order
AUF_POS.GLAS2
or
AUF_POS.GLAS3
.

Is it possible to do
OR
, or what is the best method to write this? I would like to stay away from having 3
SELECT
and then merging them as that seems messy.

Example
AUF_KOPF
rows:

AUF_NR ERFASS_DAT
1609183 06/01/2016
1609184 06/01/2016
1609185 06/01/2016
1609187 06/01/2016
1609188 06/01/2016


Example
AUF_POS
rows:

AUF_NR AUF_POS GLAS1 GLAS2 GLAS3
1609183 4 4 9 0
1609184 5 4 9 0
1609185 6 4 9 0
1609187 7 4 9 0
1609188 8 4 9 0


Desired output in this instance would show all products as
06/01/2016
, but with more data it would show the latest date, at any position, that the product ID was used.

Answer

You can use UNION ALL which shouldn't over complicate (besides the longer code)

Select Max(AUF_KOPF.ERFASS_DAT),
       AUF_POS.GLAS
From AUF_KOPF
  Inner Join (SELECT AUF_NR,GLAS1 as glas FROM AUF_POS UNION ALL
              SELECT AUF_NR,GLAS2         FROM AUF_POS UNION ALL
              SELECT AUF_NR,GLAS3         FROM AUF_POS) AUF_POS
  On AUF_KOPF.AUF_NR = AUF_POS.AUF_NR
Group By AUF_POS.GLAS
Order By AUF_POS.GLAS
Comments