Rewriting a Select as a Group By

[JaybeeinTekTips]

Hi all,

I need a final tweak I need for this code, what I need now is for firstnames, surnames, servicenames and some other generic data to be returned (again, this data is ALL contained within the function and tables - I checked!) and grouped by distinct accountID. I'm sure it'll involve a Group by statement, but I'm not sure where it goes. I imagine my code will look similar to this;


Select top 100 percent

tblServicesTree.serviceID ,
tblServicesTree.serviceName ,
tblAccounts.accountID ,
tblAccounts.ntUserDomain ,
tblAccounts.ntUserID ,
tblAccounts.accountEnabled ,
tblAccounts.accountFirstName ,
tblAccounts.accountLastName ,
tblAccounts.accountSubscriptionsEnabled

FROM dbo.fn_getValidSubscriptions() as vs
JOIN tblAccounts on tblAccounts.accountID = vs.accountID
JOIN tblServicesTree on tblServicesTree.serviceID = vs.serviceID
WHERE tblAccounts.accountEnabled <> 0 AND tblAccounts.accountSubscriptionsEnabled <> 0
AND tblAccounts.ntUserID NOT IN ('Domain1','Domain2','Domain3')

CURRENT RESULT SET:
serviceID, serviceName, accountID, ntUserDomain, ntUserID, accountEnabled, accountFirstName,

167........Multi SIM.......50194........20-uk.............puckern........1.................Natasha Pucker 1
58.........HARMS..........36394.........20-UK............SuUTERG.........1................Gordon Suuter 1
161........Prknet..........31547.........20-UK............EVAND5.........1.................Dennis Evan 1
84.........SURFS/.........34388.........20-UK............MILbergK........1.................Kevin Milberg 1

 

[AlexCuse]

The problem you are facing now is that you need to have every column selected either used in an aggregate function (min/max/sum/etc..) or in your GROUP BY. Of course, if you group by every column, you will still probably have some duplicates showing up. If you want to use GROUP BY, you will need to think of a way to identify the row you want to display.

Here's what I would do.

1. Write a subquery to return the minimum or maximum primary key (looks in this case to be ServiceID) grouped by firstname/lastname.

2. Inner join this subquery to your existing query to ensure that only rows containing a serviceID from your query will be displayed.

I hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[monksnake]

Are you sure you want this information grouped by? Based on what your post, you can use an ORDER BY and get what you need, assuming no duplicate rows.



<.

 

[JaybeeinTekTips]

The most important thing is that I get back a list of Servicenames for AccountID's.

Each AccountID has an average of 27 ServiceNames. This will be a lot of redundant data, but it's for reporting purposes. I'm going to dump it out to an .xls and relay it as requested, what happens to it after that ain't my problem...

I'm not great with syntax (being a production man!) where would the Group/Order By commands be placed?

 

[JaybeeinTekTips]

I thought the Group By should be placed at the end and I was right!!

Thanks for your help!

Jaybee