USE DISTINCT in WHERE CLAUSE - SQL Server Forums

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

General SQL Server Forums

Database Design and Application Architecture

USE DISTINCT in WHERE CLAUSE

Author

Topic

pinnaclejennie
Starting Member

1 Post

Posted - 2011-07-25 : 22:07:54

I am running into a huge road block with running SQL queries. I want to use functions like DISTINCT and MAX and such in the WHERE portion of the query, but I can't figure it out. I understand it doesn't work there, but I don't understand how that works. If someone can help me to understand how I can use that function or a similar function to produce the results I need. Example. I would like to run a query where I get the SUM of a value, but only when the transaction number is distinct. I would like to run a query where I get the sum of a value, based on the MAX instance of an entry....for instance, the value based on the max date. If that makes any sense, I desperately need help ASAP.

Example:
SELECT SUM (transaction_amount) WHERE transaction_date is MAX(transaction_date) ==> for all accounts in table

PLEASE HELP!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-25 : 23:11:30

A correlated subquery will do the trick

SELECT	t.AccountID, SUM(t.transaction_amount)
FROM	myTable t
JOIN	(
	SELECT	AccountID, MAX(transaction_date) maxDate
	FROM	myTable
	GROUP BY
		AccountID
) x
On	x.AccountID = t.AccountID
And	x.maxDate = t.transaction_date
GROUP BY
	t.AccountID;
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources