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 clauseSELECT 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 clauseSELECT 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 |
 |
|
|
|
|