cancel
Showing results for 
Search instead for 
Did you mean: 

Query Select MAX with union all

Jos_Dielemans
Active Participant
0 Kudos

I have a Query that lists all used items in quetations, orders, deliveries and invoices within a range, eg test0001 until test9999. Now I want to get the highest number of these items which are used in one of the marketing documents.

How do I get the MAX statement on the following query so that I get only the highest number in the list?

SELECT T0.[ItemCode] as 'Artikelnr', T0.[DocDate] as 'Datum', T0.[ObjType] as 'DocType', T0.[DocEntry] as 'Doc-ID', T0.[LineNum] as 'Regelnr'FROM RDR1 T0 WHERE T0.[ItemCode] >= 'TEST0001' and T0.[ItemCode] < 'TEST9999'

UNION ALL

SELECT T1.[ItemCode] as 'Artikelnr', T1.[DocDate] as 'Datum', T1.[ObjType] as 'DocType',T1.[DocEntry] as 'Doc-ID', T1.[LineNum] as 'Regelnr'FROM DLN1 T1 WHERE T1.[ItemCode] >= 'TEST0001' and T1.[ItemCode] < 'TEST9999'

UNION ALL

SELECT T2.[ItemCode] as 'Artikelnr', T2.[DocDate] as 'Datum', T2.[ObjType] as 'DocType',T2.[DocEntry] as 'Doc-ID', T2.[LineNum] as 'Regelnr' FROM INV1 T2 WHERE T2.[ItemCode] >= 'TEST0001' and T2.[ItemCode] < 'TEST9999'

UNION ALL

SELECT T3.[ItemCode] as 'Artikelnr', T3.[DocDate] as 'Datum', T3.[ObjType] as 'DocType',T3.[DocEntry] as 'Doc-ID', T3.[LineNum] as 'Regelnr' FROM QUT1 T3 WHERE T3.[ItemCode] >= 'TEST0001' and T3.[ItemCode] < 'TEST9999'

ORDER BY 'Artikelnr' DESC

Accepted Solutions (0)

Answers (2)

Answers (2)

KonradZaleski
Active Contributor

If understood you correctly, you want to return max item number ('Artikelnr') for each document ('Doc-ID'). Then the query will look like:

SELECT 
'Doc-ID',
MAX('Artikelnr') as 'Max Item'
FROM
(
SELECT T0.[ItemCode] as 'Artikelnr', T0.[DocDate] as 'Datum', T0.[ObjType] as 'DocType', T0.[DocEntry] as 'Doc-ID', T0.[LineNum] as 'Regelnr'FROM RDR1 T0 WHERE T0.[ItemCode] >= 'TEST0001' and T0.[ItemCode] < 'TEST9999'
UNION ALL
SELECT T1.[ItemCode] as 'Artikelnr', T1.[DocDate] as 'Datum', T1.[ObjType] as 'DocType',T1.[DocEntry] as 'Doc-ID', T1.[LineNum] as 'Regelnr'FROM DLN1 T1 WHERE T1.[ItemCode] >= 'TEST0001' and T1.[ItemCode] < 'TEST9999'
UNION ALL
SELECT T2.[ItemCode] as 'Artikelnr', T2.[DocDate] as 'Datum', T2.[ObjType] as 'DocType',T2.[DocEntry] as 'Doc-ID', T2.[LineNum] as 'Regelnr' FROM INV1 T2 WHERE T2.[ItemCode] >= 'TEST0001' and T2.[ItemCode] < 'TEST9999'
UNION ALL
SELECT T3.[ItemCode] as 'Artikelnr', T3.[DocDate] as 'Datum', T3.[ObjType] as 'DocType',T3.[DocEntry] as 'Doc-ID', T3.[LineNum] as 'Regelnr' FROM QUT1 T3 WHERE T3.[ItemCode] >= 'TEST0001' and T3.[ItemCode] < 'TEST9999'
) ALL_DOCS
GROUP BY
'Doc-ID'
Jos_Dielemans
Active Participant
0 Kudos

Hello Conrad; Thanks for your answer; but somehow this does not work: The table remains empty.

And I am looking for the max ItemCode (Artikelnr) over ALL the documents: so not per document.