Join with goup by

[Ultradiv]

Hi folks
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?

Sorry if it's not clear.

 

[r937]

select c.cid
     , c.something
     , t.p     
  from clients as c
inner
  join (
       select Count(*) as p
            , cid 
         from @t
       group 
           by cid
       ) as t
    on t.cid = c.cid
order
    by t.p desc

r937.com | rudy.ca

 

[PHV]

SELECT C.*, G.p
FROM tblClients C INNER JOIN (
select Count(*) as p,cid from @t group by cid
) G ON C.cid = G.cid
ORDER BY G.p DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Ultradiv]

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

best regards
Andy