Using a group by and a join

[Ultradiv]

Sorry but I posted this in the ANSI forum first without realising it.

Hi folks
using SQL Server 2000
I am having difficuly sussing out how to join while using a group by clause.

table one is a variable (@t)
constructed thus:

declare @t table
([id] int IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
cid int)

I do some manipulations on it then I select a results list thus:

select Count(*) as p,cid from @t
group by cid
order by p desc

which gives me the number of times cid appears in @t for each cid as p and cid itself. All okay so far.

cid is a primary key in the clients table and I want to pull all the client data for each cid (row) in the results
how do I construct the join?
any suggestions?

 

[gmmastros]

Use a derived table, like this...

Select Clients.*,
       SomeAlias.p
From   Clients
       Inner Join [!]([/!][blue]
         select Count(*) as p,
                cid 
         from   @t
         group by cid
         order by p desc [/blue]
         [!]) As SomeAlias[/!]
         On Clients.cid = SomeAlias.cid

Notice the part in blue is exactly the same as your query.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Ultradiv]

Hey guys thanks very much I now have it working!
Donations on the way

best regards
Andy