Distinct IDs from one table for inner join SQL

I’m trying to take the distinct IDs that appear in table a, filter table b for only these distinct IDs from table a, and present the remaining columns from b. I’ve tried:

SELECT * FROM 
(
    SELECT DISTINCT
        a.ID,
        a.test_group,
        b.ch_name,
        b.donation_amt

    FROM table_a a 
        INNER JOIN table_b b 
        ON a.ID=b.ID
    ORDER by a.ID;
) t

This doesn’t seem to work. This query worked:

SELECT DISTINCT a.ID, a.test_group, b.ch_name, b.donation_amt
FROM table_a a
    inner join table_b b
    on a.ID = b.ID
order by a.ID

But I’m not entirely sure this is the correct way to go about it. Is this second query only going to take unique combinations of a.ID and a.test_group or does it know to only take distinct values of a.ID which is what I want.

Answer

Your first and second query are similar.(just that you can not use ; inside your query) Both will produce the same result.

Even your second query which you think is giving you desired output, can not produce the output what you actually want.

Distinct works on the entire column list of the select clause.

In your case, if for the same a.id there is different a.test_group available then it will have multiple records with same a.id and different a.test_group.