Skip to Content
0
Dec 02, 2018 at 02:37 PM

Query Select MAX with union all

254 Views Last edit Dec 02, 2018 at 02:39 PM 2 rev

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