Mark Top Record with 1 - 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 2000 Forums

Transact-SQL (2000)

Mark Top Record with 1

Author

Topic

NickyJ
Starting Member

46 Posts

Posted - 2008-03-12 : 09:37:49

Hi all,

I have a recordset of say

TotalSales SalesPerson Month

100 Bob March
250 Jim March
130 Sam March

I need to mark the highest TotalSales with say a 1, so as above would look like below :

TotalSales SalesPerson Month Top

100 Bob March 0
250 Jim March 1
130 Sam March 0

Tried having clause with max but to no avail....

NickyJ
Starting Member

46 Posts

Posted - 2008-03-12 : 09:52:55

Have answered this myself in the end using below

SELECT x.ClientName,
x.GP,
p.GP,
case when x.GP / p.GP < 1 Then 0 else x.GP / p.GP end as Wedge
FROM (
SELECT ClientName,
SUM(GP) AS GP
FROM vw_TimesheetProfitCalcsByClient
where AllocatedYear = '2008'
and AllocatedMonth = 'March'

GROUP BY ClientName
) AS x
CROSS JOIN (
SELECT MAX(GP) AS GP
FROM vw_TimesheetProfitCalcsByClient
where AllocatedYear = '2008'
and AllocatedMonth = 'March'

) AS p

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources