Need Order By Then Group - 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

SQL Server Development (2000)

Need Order By Then Group

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-10-03 : 13:39:39

Quinn writes "SQL Server 8.00.194, Win2K Pro Service Pack 1.

I am working on a query to produce a result set for printing mailing labels in Crystal Reports.

The query I have so far returns the results I need for the labels (condensed):


AddressId   LastName     FirstName    Address
---------   --------     ---------    --------------------
1           Smith        Mary         123 This Street
1           Jones        Harold       123 This Street
2           Powers       Reginald     456 That Street
2           Powers       Nancy        456 That Street
3           Johnson      Greg         789 The Other Street

etc.

Now, only one label should go out for each AddressId, which Crystal Reports does nicely by grouping names on addressId. However, a requirement is that the labels must be sorted by last name of one (it doesn't matter which) of the parents. The sample above should come out:

Greg Johnson
789 The Other Street

Reginald Powers
Nancy Powers
456 That Street

Mary Smith
Harold Jones
123 This Street

I can't do a straight forward sort on last name because then that throws the grouping off. What would be nice is an aggregate concatenation on strings like "concat(LastName, ' ', FirstName) as Parents", so the results would be:


Parents                        Address
----------------------------   -------------------
Smith Mary Jones Harold        123 This Street
Powers Reginald Powers Nancy   456 That Street
Johnson Greg                   789 The Other Street

I could just sort asc on Parents. Any ideas?

Here is the entire query. I think you can ignore the subqueries, unless someone sees a better way. An Account can have zero or more AccountAddresses, which joins Addresses to Parents.

SELECT
AccountAddress.id,
Parent.lastName,
Parent.firstName,
Address.address1,
Address.address2,
Address.address3,
Address.city,
Address.state,
Address.zip1,
Address.zip2,
School.name,
School.code,
isDropped,
sessId
FROM Account, AccountAddress, Address, Parent,
( SELECT child.acctId as acctId, school.name as name, school.code as code
FROM School, Child where Child.schId = School.id) As School,
( SELECT child.acctId as acctId,
CASE
WHEN MAX(dropDate) < current_timestamp AND MIN(ISNULL(dropDate,0)) != cast('1900-01-01' as DATETIME) THEN 1
ELSE 0
END
as isDropped FROM Child GROUP BY acctId) AS Dropped
WHERE Account.id = AccountAddress.acctId
AND AccountAddress.addrId = Address.Id
AND AccountAddress.id = Parent.acAdrId
AND Account.id = School.acctId
AND Account.id = Dropped.acctId
AND Account.sessId = 2
GROUP BY
AccountAddress.id,
Parent.lastName,
Parent.firstName,
Address.address1,
Address.address2,
Address.address3,
Address.city,
Address.state,
Address.zip1,
Address.zip2,
School.name,
School.code,
isDropped,
sessId

"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources