SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Aggregate functions
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jcarrallo
Starting Member

Spain
31 Posts

Posted - 06/13/2014 :  14:11:15  Show Profile  Reply with Quote
Hi Guys,

Thanks for your great work.

I have a simple query like:

select max(PTR_DATE)
from MPR_portfolio_transactions
group by PTR_SYMBOL

and this is working fine, but if I add an extra column with another field like:

select max(PTR_DATE) , PTR_SHAREBALANCE
from MPR_portfolio_transactions
group by PTR_SYMBOL

Then I get an error message like:

Column 'MPR_portfolio_transactions.PTR_SHAREBALANCE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How can I get round to this?
Please help!
Jay

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 06/13/2014 :  14:15:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
What do you want to do with the PTR_ShareBalance column?



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

jcarrallo
Starting Member

Spain
31 Posts

Posted - 06/13/2014 :  14:21:36  Show Profile  Reply with Quote
Just to show the resulting number. Let me give you and example:

Lets say I have 2 records:

symbol= GOOG
PTR_DATE= 01/01/2014
PTR_SHAREBALANCE= 1

Symbol= GOOG
PTR_DATE= 02/01/2014
PTR_SHAREBALANCE= 2

The max(PTR_DATE) function will give me the result of 02/01/2014. Next to it I want to show PTR_BALANCE for that record ie: 2

Sio the result of the query would be;

02/01/2014 | 2

thank you

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 06/13/2014 :  14:26:35  Show Profile  Reply with Quote
You can use the ROW_NUMBER function like shown below:
;WITH cte AS
(
	SELECT 
		Symbol,
		PTR_DATE,
		PTR_SHAREBALANCE,
		ROW_NUMBER() OVER(PARTITION BY Symbol ORDER BY PTR_DATE DESC) AS RN -- construct a row number
	FROM
		YourTable
)
SELECT Symbol, PTR_DATE,PTR_SHAREBALANCE 
FROM cte
WHERE RN = 1;
You could also use a join or a cross apply, but this is likely to be more efficient than those methods.
Go to Top of Page

jcarrallo
Starting Member

Spain
31 Posts

Posted - 06/13/2014 :  14:29:45  Show Profile  Reply with Quote
really cool!!!
many thanks
Go to Top of Page

MuralikrishnaVeera
Posting Yak Master

India
106 Posts

Posted - 06/14/2014 :  01:20:12  Show Profile  Reply with Quote
We can do it in a single line ..Try learning about USING PARTITION CLAUSE WITH AGGREGATE FUNCTIONS

SELECT  MAX(PTR_DATE) OVER (PARTITION BY  PTR_SYMBOL) AS PTR_DATE
      , PTR_SHAREBALANCE
FROM    MPR_portfolio_transactions


---------------
Murali Krishna

You live only once ..If you do it right once is enough.......
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 06/14/2014 :  04:48:54  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Why would you do that? In your code you will show the same date for all balances.
Kind of weird, if you ask me. Not to mention you are not getting the result original poster asked for.




Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000