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