group but only take top 1 name

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)

group but only take top 1 name

Author

Topic

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-07-29 : 10:15:05

How do I only list 1 row in a result set made up of this:


SELECT 	companyname, parentgroup from companies group by companyname, parentgroup order by companyname


BARRATT EASTERN COUNTIES	ZBAR0250
BARRATT EASTERN COUNTIES LIMITED	ZBAR0250

Instead of 2 rows, I want it to list the top name and group it by its parent code.


BARRATT EASTERN COUNTIES	ZBAR0250

There is a data nomaly causing this with name changes so I have to deal with it as is as I am unable to correct the source.


declare @topnum int,
@month int
set @topnum = 500
set @month = 0

SET ROWCOUNT @topnum

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

SET ROWCOUNT 0

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-29 : 10:26:59

quote:
the top name

How are you defining this? SQL Server has no concept of top, unless there is some ordering. Could it be as simple as:

SELECT max(companyname) as companyname, parentgroup
from companies group by parentgroup order by companyname

?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-29 : 10:28:08

Oh. You've edited your post. I don't know what you're trying to tell us now that the query is there.

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-07-29 : 10:37:00

The query brings back data from a table and instead of grouping on the accountcode, it groups on the parentgroup.


BARRATT EASTERN COUNTIES	ZBAR001     ZBAR0250
BARRATT EASTERN COUNTIES LIMITED	ZBAR0250     ZBAR0250
BARRATT EASTERN COUNTIES LTD     ZBAR0250     ZBAR0250

Now my problem is that as soon as I add in the companyname to the grouping, it splits the above back into 3 separate records.
I need it to only list one of the companynames in the sample above but group everything together by the parentgroup column in the join to another table.

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-07-29 : 10:42:36

So, now I have this but some of the figures seems like they're adding in a bit:


declare @topnum int,
@month int
set @topnum = 500
set @month = 0

SET ROWCOUNT @topnum

SELECT		Groups.CompanyName,
		Groups.ParentGroup,
		sum(Amounts.Turnover) AS Turnover
FROM

(SELECT max(companyname) as companyname, parentgroup
from companies group by parentgroup ) Groups

LEFT JOIN (

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

GROUP BY 	Groups.CompanyName,
		Groups.ParentGroup
ORDER BY 	Amounts.Turnover desc

SET ROWCOUNT 0

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-07-29 : 11:22:28

seems to work.
SQL okay ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources