select top instead of max - 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)

select top instead of max

Author

Topic

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-08-19 : 11:39:25

Have a problem with the below stored procedure.
In a table of companynames


accountcode, parent, name
ZBOV0001 ZBOV0001 Big Company
ZCAT0001 ZBOV0001 Cat Company

The following stored procedure lists the name as Cat Company instead of the alphabetical Big Company.
I cannot order the select max(companyname) name part because SQL won't allow me. ANy workarounds?


--web_TopClientsAll 500, 0, 1
CREATE PROCEDURE web_TopClientsAll (

@topnum int,
@month int,
@sector int,
@monthend int

)

AS

SET ROWCOUNT @topnum

SELECT		Groups.CompanyName,
		Groups.ParentGroup,
		sum(Amounts.Turnover) AS Turnover,
		ClientSectors.Description,
		Groups.TAP,
		Groups.KAM,
		Groups.KAD
FROM

(SELECT max(companyname) as companyname, parentgroup, ClientSector, TAP, KAM, KAD
from companies group by parentgroup, ClientSector, TAP, KAM, KAD ) Groups 

LEFT JOIN (

SELECT 		C.ParentGroup,
		[amtin loccur] AS Turnover
FROM 		Companies C
LEFT JOIN	zarinvreg ON zarinvreg.Customer = C.AccountCode
WHERE 		monthdata <= @month
AND		monthdata >= @monthend--@month-11 --this gives us 12 months
AND 		C.CompanyName <> '-'
AND 		C.CompanyName is not null
) Amounts ON Groups.ParentGroup = Amounts.ParentGroup

LEFT JOIN ClientSectors ON ClientSectors.ID = Groups.ClientSector
WHERE (ClientSectors.ID = @sector OR ISNULL(@sector ,'')='')
AND Amounts.Turnover is not null

GROUP BY 	Groups.CompanyName,
		Groups.ParentGroup,
		ClientSectors.Description,
		Groups.TAP,
		Groups.KAM,
		Groups.KAD
ORDER BY 	Amounts.Turnover desc

SET ROWCOUNT 0





GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-19 : 11:45:58

ORDER BY sum(Amounts.Turnover)
?

E 12°55'05.25"
N 56°04'39.16"

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-08-19 : 11:59:02

No, that's fine as it is a report ordered by turnover descending.
The column Groups.CompanyName needs to pick up the first in the alphabet rather than a random number based on the parentgroup.
So, in:
ZBOV0001 ZBOV0001 Big Company
ZBOV0001 ZBOV0001 Cat Company
ZAAAAAAA ZAAAAAAA Z Company
ZBBBBBBB ZAAAAAAA A Company

It should pick up ZBOV0001 Big Company and ZAAAAAAA A Company
instead of at the moment, which is random based on the select max sub statement

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-08-19 : 12:01:33

What about using MIN instead of MAX?

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-08-19 : 12:11:20

quote:
Originally posted by Van

What about using MIN instead of MAX?

That's just flipping the problem on its head. It creates the same issue with different data by displaying Big Company instead of Cat company.
I need a way of ordering the data in the subselect but you can't do that without top.
Maybe a temporary table is the only option?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 12:16:53

quote:
Originally posted by qwertyjjj

quote:
Originally posted by Van

What about using MIN instead of MAX?

That's just flipping the problem on its head. It creates the same issue with different data by displaying Big Company instead of Cat company.
I need a way of ordering the data in the subselect but you can't do that without top.
Maybe a temporary table is the only option?

so for each parent you want to return record with alphabetically first name value?

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-08-19 : 12:22:39

quote:
Originally posted by visakh16

quote:
Originally posted by qwertyjjj

quote:
Originally posted by Van

What about using MIN instead of MAX?

That's just flipping the problem on its head. It creates the same issue with different data by displaying Big Company instead of Cat company.
I need a way of ordering the data in the subselect but you can't do that without top.
Maybe a temporary table is the only option?

so for each parent you want to return record with alphabetically first name value?

Yep, and at the moment this:


(SELECT max(companyname) as companyname, parentgroup, ClientSector, TAP, KAM, KAD
from companies group by parentgroup, ClientSector, TAP, KAM, KAD ) Groups

just brings it back randomly and you can't use order bys in subselects apparently.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources