SQL select code to show records with highest Delivery number

kevin1983
kevin1983 used Ask the Experts™
on
Hi,
I have the below basic sql select code but id like to only display the highest DeliveryNum records related to the the DocNum in the results. Ie only the DocNum line with the highest DeliveryNum number record shown

For example at the moment the results show 2 records both with Docnum 46182 and one record with DeliveryNum 2309 and the other record with 2320, but id like the results to only show one line with the delivery number: 2320

Is this possible?


SELECT [TrgetEntry]
      ,[DocEntry]
      ,[DocNum]
      ,[NumAtCard]
      ,[DeliveryNum]
      ,[CardCode]
      ,[ShipToCode]
      ,[Address]
      ,[SalesDocDelivery]
  FROM [LiveDB].[dbo].[View_Driver]
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
Here you go.  Assumes DeliveryNum is numeric.
btw I'd recommend renaming the Address column, as it's a reserved word in SQL and forces you to use square brackets [ ] to refer to it in T-SQL.

SELECT
      TrgetEntry,
      DocEntry,
      DocNum,
      NumAtCard,
      Max(DeliveryNum) as highest_delivery_number,
      
      CardCode,
      ShipToCode,
      [Address],
      SalesDocDelivery
FROM LiveDB.dbo.View_Driver
GROUP BY
      TrgetEntry,
      DocEntry,
      DocNum,
      NumAtCard,
      CardCode,
      ShipToCode,
      [Address],
      SalesDocDelivery

Author

Commented:
great, Thanks  jimhorn and the recommendation on the address field
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Thanks for the grade.  Good luck with your project.  -Jim

Author

Commented:
Thanks, I had a little search on-line for reserved t-sql keywords but couldn't see address listed unless the lists I found were not up to date and address is a more recent word?.

http://msdn.microsoft.com/en-us/library/ms189822.aspx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial