[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Filter by highest value

Posted on 2011-10-06
7
Medium Priority
?
332 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:David11011
  • 3
  • 2
  • 2
7 Comments
 
LVL 23

Accepted Solution

by:
wdosanjos earned 2000 total points
ID: 36925326
Select CustNum, Max(ShipDate) ShipDate From YourTable
   Group By CustNum
   Order By CustNum
0
 
LVL 2

Author Comment

by:David11011
ID: 36925523
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
0
 
LVL 5

Expert Comment

by:almander
ID: 36925716
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

   
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 23

Expert Comment

by:wdosanjos
ID: 36925717
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

0
 
LVL 5

Expert Comment

by:almander
ID: 36925730
Select CustNum,
 (SELECT Max(SUB_TAB.ShipDate) FROM  YourTable SUB_TAB WHERE SUB_TAB.CustNum = YourTable.CustNum)  LAST_SHIP_DATE,
  ShipDate ,
  *
From YourTable
0
 
LVL 23

Assisted Solution

by:wdosanjos
wdosanjos earned 2000 total points
ID: 36925734
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

0
 
LVL 2

Author Comment

by:David11011
ID: 36926115
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

0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question