select max figure $ - 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

SQL Server 2012 Forums

Transact-SQL (2012)

select max figure $

Author

Topic

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2014-03-17 : 14:57:11

Hello,

Please help me to write sql statement to select the max figure of the year.

For example: Please see below sample data, I like to choose final result in red color.

Acct Fig1 Fig2 Year
111222333 $11,073.76 $360,705.86 2011
111222333 $8,724.48 2013
111222333 $9,677.96 $315,241.07 2012

I used this query, but result is correct for the figures, but giving the wrong year...
select acct, max(fig1) as fig1, max(fig2) as fig2, max(year) as year
from table1
group by acct

Thanks,

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-17 : 15:38:07

Use the TOP clause

SELECT TOP(1) acct, fig1, fig2, year
FROM table1
ORDER BY fig1 DESC

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2014-03-18 : 07:25:25

Top clause is for certain # of row you want to select?It will work for single account (as example) . but there are multiple accounts that I need to select for highest fig, then it won't work ....

quote:
Originally posted by James K

Use the TOP clause
SELECT TOP(1) acct, fig1, fig2, year
FROM table1
ORDER BY fig1 DESC

tran008
Starting Member

38 Posts

Posted - 2014-03-18 : 11:14:28

IF Object_id('tempdb..#tmp1') IS NOT NULL
DROP TABLE #tmp1

select * into #tmp1 from (
select '111222333' as account, 11073.76 as fig1, 360705.86 as fig2, '2011' as [year]
union all
select '111222333', 8724.48 , 0 , '2013'
union all
select '111222333' , 9677.96 , 315241.07, '2012'
)b

;with ws1
as
(
select (Row_number() OVER(partition BY account ORDER BY (fig1+fig2) desc)) seq, Account, Fig1, Fig2, [Year] from #tmp1)

select * from ws1 where seq=1

DROP TABLE #tmp1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources