Using DISTINCT with multiple tables

1

Hello,

I'm not using the exact column names below, but I'm able to get the results I'm looking for. Now, I want to use another table (Table3) and a memo column but I notice it doesn't work when I use the DISTINCT. I am new to writing SQL queries. Let me know if you need more information.

Thank you ~ JD

Works
SELECT DISTINCT
a."Colunm1", a."Column2", a."Column3", b."Column8
FROM Table1 a
INNER JOIN
Table2 b
ON a."RecordNumber"=b."RecordNumber"
ORDER BY "USERNUMBER" ASC;

Works
SELECT DISTINCT a."Column1"
FROM Table1 a

This is a memo field. Does not work
SELECT DISTINCT c."Summary"
FROM Table3 c

2

Can you post more details on what you mean when you say "it doesn't work"? Do you get an error message? If so what is the error message? Or, does it produce an output that contains duplicates?

If the data type of the column is text, you cannot use distinct. In that case you will get an error message. If that is the case, replace a."Column1" with CAST(a."Column1" AS VARCHAR(MAX))

If you are getting duplicates, it may be due to collation of the column is different from what you are expecting it to be, or perhaps there are hidden characters that make it appear as though they are duplicates when in fact they are not.

1 Like
3

Please define "does not work"

4

Thanks for the quick response. Here's what I'm running and I'm happy with the results.

SELECT DISTINCT
a."ProtocolNumber", a."PI", a."Title", b."FundingName", a."ApprovalStart", a."ApprovalEnd", a."OriginalApprovalDate",
a."Status"
FROM REPORTING_DATA a
INNER JOIN
REPORTING_FUNDINGSOURCE b
ON a."RecordNumber"=b."RecordNumber"
ORDER BY "ProtocolNumber" ASC;

Now I want to add this column to this report
SELECT c."Laysummary"
FROM REPORTING_SUMMARY

I'm trying to use the following
SELECT DISTINCT
a."ProtocolNumber", a."PI", a."Title", b."FundingName", a."ApprovalStart", a."ApprovalEnd", a."OriginalApprovalDate", c"Laysummary",
a."Status"
FROM REPORTING_DATA a
INNER JOIN
REPORTING_FUNDINGSOURCE b
ON a."RecordNumber"=b."RecordNumber"
INNER JOIN
REPORTING_SUMMARY c
ON a."RecordNumber"=c."RecordNumber"
ORDER BY "ProtocolNumber" ASC;

Error message when I use the following
SELECT DISTINCT c."Laysummary"
FROM REPORTING_SUMMARY c

ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
Error at Line: 16 Column: 17

5

I'm getting the error message below when I'm trying to use DISTINCT with a text column. What else can I use besides DISTINCT that will not return dups and allow me to see the text column below?

Thank you

6

Is this for Microsoft sql server?

7

This looks like MySql...

This is a situation where DISTINCT isn't going to work - but you should be able to use GROUP BY and MAX.

You just have to group by all columns except Laysummary - and take the max of that column.

8

Thanks, jeffw8713

I'll give the GROUP BY and max column and try

9

I'm using oracle sql developer

10

Thank for your help guys.

I get the results below which is what I'm looking for using this query.
SELECT DISTINCT a."APT_ID", a."BLDG", a."UNIT", a."UTILITIES", b."ADDRESS", b."CITY"
FROM APT_INFO a
INNER JOIN BLDG_INFO b
ON a."APT_ID"=b."APT_ID"
ORDER BY "BLDG" ASC;
|AP01|BKBS|A1|Heat|73 Laidlaw Avenue|Brooklyn|
|AP02|BKBS|A2|Heat|73 Laidlaw Avenue|Brooklyn|
|AP03|BKBS|A3|Heat|73 Laidlaw Avenue|Brooklyn|
|AP04|BKBS|A4|Heat|73 Laidlaw Avenue|Brooklyn|

How can I add this query or text column to the report above? The data type for SUMMARY is text.
SELECT DISTINCT CAST (c."SUMMARY" AS VARCHAR(MAX)) AS SUMMARY
FROM TEST c
|1|NULL
|2|This is just a test to see how much data I can add to a text field