I've struggled with a MS SQL QUERY for a while and realize that I have to get help from someone who knows MS SQL (much) better than I do.

Environment: MS SQL 2008

Code: [Select]SELECT
Levfaktura.LfhAnm AS [SUPPL.ORDERNO],
SUBSTRING(ISNULL(CAST(Orderhuvud.OrhOnr AS VARCHAR), ''), 1, 8) AS [OUR ORDER],
SUBSTRING(ISNULL(CAST(Faktura.FahFnr AS VARCHAR), ''), 1, 7) AS [CUST.INVOICE]
FROM Levfaktura

LEFT OUTER JOIN Orderhuvud
ON Levfaktura.LfhAnm = Orderhuvud.OrfAnm5

LEFT OUTER JOIN
Faktura ON Orderhuvud.OrfAnm5 = Faktura.FahAnm5

GROUP BY Levfaktura.LfhAnm, Orderhuvud.OrhOnr, Faktura.FahFnr
ORDER BY [SUPPL.ORDERNO],[CUST.INVOICE]

This is the result I get with this query:

SUPPL.ORDERNO OUR ORDER CUST.INVOICE
------------------------------ --------- ------------
143914 64228 179890
143914 64228 179935
143914 64228 179995
143914 64228 179999
143914 64228 180067
143914 64228 180148
326042
326052 64549 180219
326086 64633

(9 row(s) affected)


I get multiple 'CUST.INVOICE' for each 'SUPPL.ORDERNO' and 'OUR ORDER' combination but I only want to return the latest 'CUST.INVOICE' (=largest invoice number).


This is the result I want to return:


SUPPL.ORDERNO OUR ORDER CUST.INVOICE
------------------------------ --------- ------------
143914 64228 180148
326042
326052 64549 180219
326086 64633

(4 row(s) affected)

• if there are more than 1 CUST.INVOICE for the combination of 'SUPPL.ORDERNO' and 'OUR ORDER' I want to show only the 'CUST.INVOICE' with the largest number
• if there is no 'OUR ORDER' and no 'CUST.INVOICE' I want to it to be shown as empty
• if there is an 'OUR ORDER' and no 'CUST.INVOICE' I want to it to be shown as empty