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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 select max figure $
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nt4vn
Yak Posting Veteran

98 Posts

Posted - 03/17/2014 :  14:57:11  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3758 Posts

Posted - 03/17/2014 :  15:38:07  Show Profile  Reply with Quote
Use the TOP clause
SELECT TOP(1) acct, fig1, fig2, year
FROM table1
ORDER BY fig1 DESC
Go to Top of Page

nt4vn
Yak Posting Veteran

98 Posts

Posted - 03/18/2014 :  07:25:25  Show Profile  Reply with Quote
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


Go to Top of Page

tran008
Starting Member

38 Posts

Posted - 03/18/2014 :  11:14:28  Show Profile  Reply with Quote

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
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.06 seconds. Powered By: Snitz Forums 2000