Oracle query to Sql server - 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 2008 Forums

Transact-SQL (2008)

Oracle query to Sql server

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2011-08-01 : 15:40:32

Can any one help me get the below query in T-sql...

I know the fisrt value here is something similar to row_number but just cnat seem to get it right

SELECT FIRST_VALUE (a.agent_no) OVER ( ORDER BY COUNT (c.agent_no)
, a.agent_no
) AS assigned_agent_no
FROM agent a
LEFT OUTER JOIN customer c ON a.agent_no = c.agent_no
GROUP BY a.agent_no
;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-02 : 00:59:28

[code]
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY a.agent_no ORDER BY COUNT (c.agent_no) , a.agent_no
) AS rn
FROM agent a
LEFT OUTER JOIN customer c ON a.agent_no = c.agent_no
GROUP BY a.agent_no
)t
WHERE rn=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources