Filter by highest value

I'm trying to find the last ship date for each customer in our database. Each customer has multiple orders in the orders table and I just need to see the last order that each customer made. Here is an example:

Imagine that I have a table like this.
CustNum              ShipDate
   123                   08-01-2011
   456                   08-01-2011
   789                   08-03-2011
   123                   08-04-2011
   123                   08-12-2011
   789                   08-09-2011

I want my query results to look like this
CustNum             ShipDate
   123                   08-12-2011
   456                   08-01-2011
   789                   08-09-2011
LVL 2
David11011Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wdosanjosCommented:
Select CustNum, Max(ShipDate) ShipDate From YourTable
   Group By CustNum
   Order By CustNum

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David11011Author Commented:
Thats putting me down the right path. The only problem I'm having now is that there are more columns in the table than I posted. When I try and do a group by it says that the other columns  are not contained in the aggregate function or the group by clause. Here is a list of the columns that I need to see from the table.

CUSTNO
DAYPHONE
COMPANYNAME
STATE
CUSTOMERTYPE
STATUS
FNAME
LNAME
FAXPHONE
SHIPDATE

The problem I'm having with the group by clause is that it will return a new row if there is different data in any of the columns.

I want to group by CUSTNO and not include the other columns in the group by clause so that rows will be dropped regardless of whether or not the other columns are unique.

I was having a hard aticulating my point. I hope that I wasn't too confusing
almanderCommented:
Something like this should work.


Select CustNum,
 (SELECT Max(SUB_TAB.ShipDate) FROM  YourTable SUB_TAB WHERE SUB_TAB.CUSTOMERID = YourTable.CUSTOMERID)  LAST_SHIP_DATE,
  ShipDate ,
  *
From YourTable

   
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

wdosanjosCommented:
Got it.  Please try the following:

Select CUSTNO, DAYPHONE, COMPANYNAME, STATE, CUSTOMERTYPE, STATUS, FNAME, LNAME, FAXPHONE, SHIPDATE
  From YourTable c
       Inner Join (Select CUSTNO, Max(SHIPDATE) SHIPDATE From YourTable Group By CUSTNO) sdt
             On sdt.CUSTNO = c.CUSTNO and sdt.SHIPDATE = c.SHIPDATE

Open in new window

almanderCommented:
Select CustNum,
 (SELECT Max(SUB_TAB.ShipDate) FROM  YourTable SUB_TAB WHERE SUB_TAB.CustNum = YourTable.CustNum)  LAST_SHIP_DATE,
  ShipDate ,
  *
From YourTable
wdosanjosCommented:
Sorry. I missed the table identifier.  Here is the corrected version:

Select c.CUSTNO, c.DAYPHONE, c.COMPANYNAME, c.STATE, c.CUSTOMERTYPE, c.STATUS, c.FNAME, c.LNAME, c.FAXPHONE, c.SHIPDATE
  From YourTable c
       Inner Join (Select CUSTNO, Max(SHIPDATE) SHIPDATE From YourTable Group By CUSTNO) sdt
             On sdt.CUSTNO = c.CUSTNO and sdt.SHIPDATE = c.SHIPDATE

Open in new window

David11011Author Commented:
Thanks for all your help guys.

I realized that the customer information is consistent throughout the table so I could add all the columns in to the group by clause. Here is the query that I came up with.
select	CUSTOMERS.CUSTNO as 'CustomerNo' ,
		CUSTOMERPHONE.DAYPHONE as 'Phone', 
		CUSTOMERS.NAMEX as 'Company', 
		CUSTOMERS.[STATE] as 'ST', 
		CUSTOMERS.CUSTTYPE as 'CT', 
		CUSTOMERS.CUSTSTATUS as 'CS', 
		CUSTOMERS.FNAME as 'First Name', 
		CUSTOMERS.LNAME as 'Last Name', 
		CUSTOMERPHONE.FAXPHONE as 'Fax',
		max(macsaleUnion.SHIPDATE) as 'Last Ship Date'
from ORDERHEADER
				inner join macsaleUnion on macsaleUnion.ORDERNO = substring(ORDERHEADER.FULLORDERNO, 0, 9) 
				inner join CUSTOMERS on ORDERHEADER.CUSTEDP = CUSTOMERS.CUSTEDP 
				full outer join CUSTOMERPHONE on ORDERHEADER.CUSTEDP = CUSTOMERPHONE.CUSTEDP
				full outer join ITEMMAST on ITEMMAST.EDPNO = macsaleUnion.edpNos
where	(ITEMMAST.ITEMNO like @itemNo + '%') 
		and (FAXPHONE <> '') 
		and (FAXPHONE <> '0')
		and (FAXPHONE <> '1')
		and (ISNUMERIC(FAXPHONE) = 1)
		and (LEN(FAXPHONE) <= 10)
		and (CUSTTYPE = 'TK' or CUSTTYPE = 'CT' or CUSTTYPE = 'PC' or CUSTTYPE = 'CO')
		and (CUSTSTATUS = 'CC' or CUSTSTATUS = 'CA' or CUSTSTATUS = 'CB' or CUSTSTATUS = 'CV' or CUSTSTATUS = 'e%' or CUSTSTATUS = 'r%')
		and ((cast(ORDERHEADER.ENTRYDATE as date) >= @startDate and cast(ORDERHEADER.ENTRYDATE as date) <= @endDate) or (cast(macsaleUnion.SHIPDATE as date) >= @startDate and cast(macsaleUnion.SHIPDATE as date) < @endDate))
group by CUSTOMERS.CUSTNO, CUSTOMERPHONE.DAYPHONE, CUSTOMERS.NAMEX, CUSTOMERS.[STATE], CUSTOMERS.CUSTTYPE, CUSTOMERS.CUSTSTATUS,CUSTOMERS.FNAME,  CUSTOMERS.LNAME, CUSTOMERPHONE.FAXPHONE

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.