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 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>
**********************************************************************
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
