Need to use DISTINCT but insure how

[markdmac]

I have created an HTA application that makes a call to a SQL server. This is querying a database used to track support calls. Cases can have a sub case. When I query I therefore get duplicate entries for those cases that have a sub case. I would like to be able to strip out the duplicates.

Unfortunately some of the fields have spaces in the names (not my database to change) so it just makes reading the query string all the more difficult.

Here is my query:

SQLCmd = "SELECT DISTINCT dbCaseInfo."&Chr(34)&"SR Number"&Chr(34)&" As SRX,dbCaseInfo."&Chr(34)&"TAM/ADC"&Chr(34)&" AS TAM, dbCaseInfo."&Chr(34)&"Case Description"&Chr(34)&" AS Description, dbCaseInfo."&Chr(34)&"Current Severity"&Chr(34)&" AS Sev, dbCaseInfo."&Chr(34)&"Current Case Status"&Chr(34)&" AS Status, dbCaseInfo.Contact AS Contact, dbCaseInfo."&Chr(34)&"Current Case Owner"&Chr(34)&" As Owner, dbCaseInfo."&Chr(34)&"Case Open (Hours) "&Chr(34)&" AS Age, dbCaseInfo."&Chr(34)&"Case Idle Time (Days) "&Chr(34)&" AS Idle FROM dbCaseInfo WHERE ((dbCaseInfo."&Chr(34)&"TAM/ADC"&Chr(34)&" = '"& UserString & "') AND (dbCaseInfo.Contract = '"& ClarifyContract & "')) AND ((dbCaseInfo."&Chr(34)&"Current Case Owner"&Chr(34)&" <> '"& UserString & "') AND ("&Chr(34)&"Open or Closed Status"&Chr(34)&" = 'Open'));"

I tried using SELECT DISTINCT in the beginning but as I understand it all columns would then be evaluated for the unique status. I only want to filter on the dbCaseInfo.SR Number field.

I also tried adding a GROUP BY dbCaseInfo.SR Number but that too did not work.

Can anyone help me with this monster of a query?

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

 

[markdmac]

I managed to work around this. Still curious of the right way to do this.

My solution:
The data being extracted was being populated to Excel, so before adding to Excel I just compared the value to the previous value entered and if it matched executed a RecordSet.MoveNext.



I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

 

[johnherman]

SELECT DISTINCT (Field1 + Field2 + Field3+....), field1, field2, field3, field5, field88, field14, etc.

In your case, you want only one case, so subcase doesn't matter. SELECT DISTINCT (SR NUmber), next field, next field....

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[markdmac]

Thanks John, so putting the field names in the parenthesis following DISTINCT makes the DISTINCT only apply to the fields in the parenthesis and all others after the comma are just regular selects?

Appreciate your help.

Thanks,

Mark