Selecting distinct values from joined results

问题:

I'm trying to get a list of unique values in a certain column in my SQL join.

My join is as follows:

select P.column1, A.column2 from Table1 P
inner join Table2 A
on P.column1=A.column1

I want to get a list of distinct values of column 1. I tried

Select distinct A.column1 from (select P.column1, A.column2 from Table1 P
inner join Table2 A
on P.column1=A.column1)

but SQL didn't like that.

回答1:

just don't select column2 if you don't need it ?

select distinct P.column1 
from Table1 P
inner join Table2 A
on P.column1=A.column1

or if for some unknown reason you really need a subquery, you need to alias it.

Select distinct s.column1 from (select P.column1, A.column2 from Table1 P
inner join Table2 A
on P.column1=A.column1) s

回答2:

May I suggest using a CTE in place of sub query?

You can re-write the query as follows:

WITH CTE
AS
(
    SELECT P.column1,
        A.column2
    FROM Table1 P
    INNER JOIN Table2 A
    ON P.column1=A.column1
)
SELECT DISTINCT Column1,
    Column2
FROM CTE

You can select as many columns from the CTE as per your requirements.