CODEWITHSUNDEEP
sqlsql-server
barry17
barry17

Reputation: 153

SQL query looking for assistance?

I have a a bunch of columns in my table:

What I'm trying to do is look for the most recent TransactionDate and take that TransactionValue (the thing here is that the latest value might be a negative number, so I need to convert it to positive). I'm actually working on a report in Access but I've been working out of SQL to get this query to work.

Here's my query:

SELECT 
     TransactionID, TransactionName, Max(abs(TransactionValue)) AS 
     HighestPrice, MAX(TransactionDate) AS LatestDate
FROM tblTransactionRecords
WHERE TransactionQTY<>0
ORDER BY TransactionName

But this doesn't give me that information. It only grabs the MAX Value of that column rather than grabbing the Max Value where the TransactionDate is the most recent.

Upvotes: 0

Views: 73

Answers (4)

Sachin
Sachin

Reputation: 40970

Try with Having clause

SELECT 
     TransactionID, TransactionName, Max(abs(TransactionValue)) AS 
     HighestPrice, MAX(TransactionDate) AS LatestDate
FROM tblTransactionRecords
GROUP BY TransactionID
Having TransactionDate = MAX(TransactionDate) and TransactionQTY<>0

Upvotes: 1

Lorenzo Gatti
Lorenzo Gatti

Reputation: 1270

Assuming there can be multiple records with the same TransactionDate, filter by date and keep only the highest value:

SELECT TOP 1
 TransactionID, TransactionName, abs(TransactionValue) AS HighestPrice, TransactionDate 
 FROM tblTransactionRecords 
 WHERE TransactionDate=MAX(TransactionDate)
 ORDER BY abs(TransactionValue) DESC

Upvotes: 1

Aaron
Aaron

Reputation: 24802

look for the most recent TransactionDate and take that TransactionValue (the thing here is that the latest value might be a negative number, so I need to convert it to positive)

<=>

take the absolute value of the TransactionValue for the most recent TransactionDate

<=>

SELECT abs(TransactionValue)
FROM tblTransactionRecords
WHERE TransactionDate = Max(TransactionDate)

Upvotes: 1

Martijn Burger
Martijn Burger

Reputation: 7543

You should add a GROUP BY statement between the WHERE and the ORDER BY indicating on which columns you want to group.

Upvotes: 1

Related Questions