group by with min - 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)

group by with min

Author

Topic

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2009-05-05 : 11:39:13

I am drawing a blank here .


SELECT tblClients.ClientID, Min(tblContractDetail.ProdDate) AS MinOfProdDate, tblContractDetail.RepID, tblContractDetail.ManagerID
FROM tblStaff AS tblStaff_1, tblStaff, tblClients INNER JOIN (tblContractDetail INNER JOIN tblContracts ON tblContractDetail.ContractID = tblContracts.ContractID) ON tblClients.ClientID = tblContracts.ClientID
GROUP BY tblClients.ClientID, tblContractDetail.RepID, tblContractDetail.ManagerID;

I am trying to get information based off the min(proddate). So basically I want to group by ClientID, and get min(proddate) and repid, and managerID for that min(proddate) per clientid.

Can anyone help quickly with this?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-05-05 : 13:01:24

See if this works. It assumes you have unique ProdDates by clientid. I left out your (nultiple) JOINs to tblStaff and your join to tblclients because they don't seem to serve any purpose.


select cd.clientid
       ,cd.prodDate AS MinOfProdDate
       ,cd.RepID
       ,cd.ManagerID
from   (
       select clientid
              ,min(prodDate) MinProdDate
       from   tblContractDetail
       group by clientid
       ) d
inner join tblContractDetail cd
       on cd.clientid = d.clientid
       and cd.ProdDate = d.MinProdDate

Be One with the Optimizer
TG

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2009-05-05 : 14:01:56

I was playing with something like that but tblcontracts holds the clientid and links to tblcontractdetail off contractid which holds the proddate etc.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-05-05 : 14:23:05

But all the columns in your SELECT clause are from tblContractDetail so why JOIN to other tables?
Did you try it? Does it work? If not and you still want help then post the specific problem you're having.

Be One with the Optimizer
TG

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2009-05-05 : 14:45:58

I think there is some confusion as I did not have all the fields I will eventually take -

In the end the select statement would be -

tblclients.clientid, tblclients.dbaname, min(tblcontractdetail.proddate), tblcontractsdetail.repid, tblcontractsdetail.managerid

Tblclients holds clientid, dbaname
tblcontractdetail holds proddate, repid, managerid
tblcontracts holds contractid, clientid

The linke between these 2 tables is tblcontracts which holds the clientid, and tblcontractid which links in tblcontractdetail and tblclients.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-05-05 : 15:23:47

There is no confusion - you just didn't post your actual requirements

I still don't see a need to JOIN to tblContracts.


select cd.clientid
       ,c.dbaname
       ,cd.prodDate AS MinOfProdDate
       ,cd.RepID
       ,cd.ManagerID
from   (
       select clientid
              ,min(prodDate) MinProdDate
       from   tblContractDetail
       group by clientid
       ) d
inner join tblClients c
       on c.clientid = d.clientid
inner join tblContractDetail cd
       on cd.clientid = d.clientid
       and cd.ProdDate = d.MinProdDate

/*
--If you need any attributes from tblContracts then include this:
inner join tblContracts ct
       on ct.contractid = cd.conctractid
*/

EDIT:
typo corrected

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources