We used rownum = 1 in Oracle as bellows:
If we migrate to Microsoft SQL Server, how to translate it?
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT to_char(STA_DATE, 'YYYY') - 1911 ACCOUNT_YR, age, fund_id, (select fund_name from dbm031fa where fund_id=dbm034fa.fund_id and rownum=1) fund_name, mge_type, IN_AMT, OUT_AMT, (IN_AMT/OUT_AMT)*100 RATIO FROM dbm034fa WHERE to_char(STA_DATE, 'YYYY') - 1911 BETWEEN '101' AND '103' AND age = '379000000A' AND fund_id='010401C00010' AND mge_type='G' |
How-To
We can use TOP N to fulfill this requirement.
The SQL statement will be amended as following:
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT YEAR(STA_DATE) - 1911 ACCOUNT_YR, age, fund_id, (select TOP 1 fund_name from dbm031fa where fund_id=dbm034fa.fund_id) fund_name, mge_type, IN_AMT, OUT_AMT, (IN_AMT/OUT_AMT)*100 RATIO FROM dbm034fa WHERE YEAR(STA_DATE) - 1911 BETWEEN '101' AND '103' AND age = '379000000A' AND fund_id='010401C00010' AND mge_type='G' |
Reference
[1] http://www.w3schools.com/sql/sql_top.asp
[2] https://msdn.microsoft.com/zh-tw/library/ms189463(v=sql.120).aspx
No comments:
Post a Comment