How do I use the MAX statement with linked tables? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How do I use the MAX statement with linked tables?

Author

Topic

mondo3
Starting Member

13 Posts

Posted - 2006-03-29 : 17:36:40

I have these 3 [tables] and fields:

[Request]..............[Submission]................[Customer]
RequestID...............SubmissionID...............CustomerID
DocketNumber..........RequestID..................CustomerName
CustomerID.............SubmissionNumber
.............................EntryDate

The tables should be linked:
Request.RequestID=Submission.RequestID
Request.CustomerID=Customer.CustomerID

For any 1 request, there is a single customer but there can be multiple submissions, where each SubmissionNumber is an incrementing number.

I would like to have a query which shows:
RequestId,DocketNumber,CustomerName,MAX(SubmissionNumber),EntryDate

ie: If there were 10 submissions for a specific request, I only want to show the last submission record.

I just can't get this to work...any ideas?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-29 : 17:55:04

You need to JOIN the table in the manor you specified, GROUP BY requestid, docketNumber, customerName. select the same columns that you group by but add the your aggregated SubmissionNumber. The only issue I see is for EntryDate. Do you want the EntryDate for the Submission row that has the max SubmissionNumber?

The blue code does most of the work. the rest just associates the results with a specific entryDate.


select	a.requestid
	,a.docketNumber
	,a.customerName
	,a.maxSubmissionNumber
	,b.EntryDate --entry date for the maxSubmissionNumber
from	(--derived table
	select	requestid
		,docketNumber
		,customerName
		,max(submissionNumber) as maxSubmissionNumber
	from	Request r
	join	Submission s
			on s.requestid = r.requestid
	join	Customer c
			on c.customerid = r.customerid
	group by requestid
		,docketNumber
		,customerName
	) a
join	submission b
		on b.requestid = a.requestid
		and b.submissionNumber = a.maxSubmissionNumber

Be One with the Optimizer
TG

mondo3
Starting Member

13 Posts

Posted - 2006-03-29 : 18:04:00

Thanks for the quick response! yes, I need the EntryDate for the record that has the max SubmissionNumber. In actual fact, there are a few additional fields in each table that I need to show (for example, there is a text "Status" field in the Submission table), but I only listed a few in each table to try to simplify the question.

mondo3
Starting Member

13 Posts

Posted - 2006-03-30 : 10:17:18

When I try your code (shown below), I get an error. ps this is in access 2003.

SELECT a.requestid,a.docketNumber,a.customerName,a.maxSubmissionNumber,b.EntryDate
FROM
(
SELECT requestid,docketNumber,customerName,MAX(submissionNumber) as maxSubmissionNumber
FROM Request r
LEFT JOIN Submission s ON s.requestid = r.requestid
LEFT JOIN Customer c ON c.customerid = r.customerid
GROUP BY requestid,docketNumber,customerName
) a
LEFT JOIN submission b ON b.requestid = a.requestid AND b.submissionNumber = a.maxSubmissionNumber

Error:
Syntax error missing operator in query expression s.requestid=r.requestid left join customer c on c.customerid=r.customerid.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-30 : 14:45:03

>>ps this is in access 2003.

woops. all the forums under "sql server forums" are specifically for MS Sql Server. I don't know the equivilant for Access. I suggest you post the sql code in a new topic in the MS Access forum and ask for help to convert it to Access sql.

I'd help you if I could but I don't know from Access...sorry

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources