SQL Aggregation of selected rows

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 2005 Forums

Transact-SQL (2005)

SQL Aggregation of selected rows

Author

Topic

gradstudent2010
Starting Member

4 Posts

Posted - 2007-10-05 : 17:29:10

I'm using CF8 and I have an SQL issue. I'm trying to display data from three tables using one query. Here is the table information with alias and data examples:

*[pkey] means it's the primary key.

Doc d - [pkey]d.docID, d.docTitle (Holds the doctitle for each docID)

EX:
111 Emp Application
222 Budget Request
333 Vacation Request

ReadData r - [pkey]r.id, r.transactID, r.docID, r.itemID (holds multiple docID for each transactID, and holds multiple itemid for each docID)

EX:
1 12345 111 1
2 12345 111 2
3 12345 111 3
4 12345 222 1
5 73624 111 1

Doctransact dt - [pkey]di.id, dt.docID, dt.transactID
(Holds multiple docID for each transactID)

EX:
1 111
2 222
3 333

I want to display:
1.) all d.docTitles WHERE d.docID = dt.docID AND dt.transactID = 1.
2.) the Max r.itemid (if any) for all rows in the above query results, WHERE r.docID = dt.docID AND r.transactID = dt.transactID

Here is the SQL I have written:

SELECT d.doctitle, r.itemid
FROM (file_doctransact dt INNER JOIN file_doc d ON dt.docID = d.docID) LEFT OUTER JOIN file_readdata r ON dt.docID = r.docID
WHERE dt.transactID = 1

Which displays: ALL r.itemid (if any) for all rows in the above query results, where r.docID = dt.docID. I don’t want to display multiple rows of [the same docTitle and different docID]. I want it to display the one with the highest docID.

I get an error when I try to use the Max() function, such as:

SELECT d.doctitle, Max(r.itemid).

Please help.

Kristen
Test

22859 Posts

Posted - 2007-10-06 : 01:36:44

"SELECT d.doctitle, Max(r.itemid)"

Add

GROUP BY d.doctitle

to the end to display Max(r.itemid).

However, you say that you want the one with the highest docID, which I reckon is a different question?

That would probably be:


SELECT X.doctitle, r.itemid
FROM
(
	SELECT d.doctitle, MAX(dt.docID) as [MAX_docID]
	FROM file_doctransact dt 
		INNER JOIN file_doc d 
			ON dt.docID = d.docID
	WHERE dt.transactID = 1
	GROUP BY d.doctitle
) AS X
	LEFT OUTER JOIN file_readdata r 
		ON r.docID = X.[MAX_docID]

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources