Can you show me a few rows of what might be in the un-grouped join so I can see what you're working with?
To envision grouping, imagine that all rows whose column values match for the *combination* of every column in the GROUP BY clause are grouped together, and that "group" becomes a single row of output. I diagrammed a conceptual model of this in The ColdFusion MX Bible, and I refer to it in our Advanced Development with ColdFusion MX and SQL Server 2000 class as "Venetian Blinds." So, if you group by just the serial number, and twenty rows have the same serial number, those twenty rows become a single group, which is output as a single row in the intermediate result set of the GROUP BY clause. If you group by both the serial number and the ProductName, you'll get a group for each unique *combination* of serial number and product name. Remember that you can't select any other columns other than those listed in the GROUP BY clause because you're not guaranteed that they are all the exact same value. You can, however, select aggregate function results because those are guaranteed to return a single result. Respectfully, Adam Phillip Churvis Member of Team Macromedia http://www.ProductivityEnhancement.com Download Plum and other cool development tools, and get advanced intensive Master-level training: * C# & ASP.NET for ColdFusion Developers * ColdFusion MX Master Class * Advanced Development with CFMX and SQL Server 2000 ----- Original Message ----- From: "Tim Blankenship" <[EMAIL PROTECTED]> To: <[email protected]> Sent: Tuesday, June 07, 2005 8:08 PM Subject: Re: [plum] SQL question I want a single row for each unique SN. On 6/7/05, Adam Churvis <[EMAIL PROTECTED]> wrote: > > Are you trying to return a single row for each unique combination of > SerialNumber, ProductName, CPUType, CPUSpeed, and MemorySize for multiple > serial numbers? If so, you should use GROUP BY on ProductName, CPUType, > CPUSpeed, and MemorySize, rather than DISTINCT on the entire selection of > columns. > > Remember that "DISTINCT discards" whereas "GROUP BY groups." > > Respectfully, > > Adam Phillip Churvis > Member of Team Macromedia > http://www.ProductivityEnhancement.com > > Download Plum and other cool development tools, > and get advanced intensive Master-level training: > > * C# & ASP.NET <http://ASP.NET> for ColdFusion Developers > * ColdFusion MX Master Class > * Advanced Development with CFMX and SQL Server 2000 > > ----- Original Message ----- > From: "Tim Blankenship" <[EMAIL PROTECTED]> > To: "Plum Email List" <[email protected]> > Sent: Tuesday, June 07, 2005 3:46 PM > Subject: [plum] SQL question > > > I know this is not a PLUM question per say but it is needed to make my > PLUM > app to work. > > Alrighty I am using DTS on MS SQL to grab some data so I can put it in my > Oracle db. > Problem is when I get the output I am returning duplicate results even > though I used DISTINCT. > So not sure where I messed up but I figured there has to be atleast a > couple > of SQL geeks on the list. > I want it to display a single SN. > > SELECT DISTINCT { fn UCASE(LTRIM(RTRIM(R_Inventory.SerialNumber))) } AS > SN, > { fn UCASE(LTRIM(RTRIM(R_Inventory.ProductName))) } AS MODEL, > { fn UCASE(LTRIM(RTRIM(R_CPU.CPUType))) } AS PROCESSOR, > { fn UCASE(LTRIM(RTRIM(R_CPU.CPUSpeed))) } AS PROCESSOR_SPD, > { fn UCASE(LTRIM(RTRIM(R_DIMMSlots.MemorySize))) } / 1024 AS MEMORY > > FROM R_Inventory INNER JOIN R_CPU > ON R_Inventory.DeviceName = R_CPU.DeviceName > INNER JOIN R_DIMMSlots > ON (R_Inventory.DeviceName = R_DIMMSlots.DeviceName) > WHERE (R_Inventory.SerialNumber <> '') > > -- > Tim Blankenship > [EMAIL PROTECTED] > www.vespri.com <http://www.vespri.com> <http://www.vespri.com> > > > ********************************************************************** > You can subscribe to and unsubscribe from lists, and you can change > your subscriptions between normal and digest modes here: > > http://www.productivityenhancement.com/support/DiscussionListsForm.cfm > ********************************************************************** > -- Tim Blankenship [EMAIL PROTECTED] www.vespri.com <http://www.vespri.com> ********************************************************************** You can subscribe to and unsubscribe from lists, and you can change your subscriptions between normal and digest modes here: http://www.productivityenhancement.com/support/DiscussionListsForm.cfm **********************************************************************
