Distinct Help

Question

Alright so I have a table, in this table are two columns with ID's. I want to make one of the columns distinct, and once it is distinct to select all of those from the second column of a certain ID.

Originally I tried:

select distinct inp_kll_id from kb3_inv_plt where inp_plt_id = 581;

However this does the where clause first, and then returns distinct values.

Alternatively:

select * from (select distinct(inp_kll_id) from kb3_inv_plt) as inp_kll_id where inp_plt_id = 581;

However this cannot find the column inp_plt_id because distinct only returns the column, not the whole table.

Any suggestions?

Edit:

Each kll_id may have one or more plt_id. I would like unique kll_id's for a certain kb3_inv_plt id.

| inp_kll_id | inp_plt_id |
|       1941 |      41383 |
|       1942 |      41276 |
|       1942 |      38005 |
|       1942 |      39052 |
|       1942 |      40611 |
|       1943 |       5868 |
|       1943 |       4914 |
|       1943 |      39511 |
|       1944 |      39511 |
|       1944 |      41276 |
|       1944 |      40593 |
|       1944 |      26555 |

Answer 1

If you do mean, by "make distinct", "pick only inp_kll_ids that happen just once" (not the SQL semantics for Distinct), this should work:

select inp_kll_id
 from kb3_inv_plt
 group by inp_kll_id
 having count(*)=1 and inp_plt_id = 581;

Answer 2

Get all the distinct first (alias 'a' in my following example) and then join it back to the table with the specified criteria (alias 'b' in my following example).

SELECT      *
FROM        (
            SELECT 
            DISTINCT    inp_kll_id 
            FROM        kb3_inv_plt 
            ) a
LEFT JOIN   kb3_inv_plt b
        ON  a.inp_kll_id = b.inp_kll_id
WHERE       b.inp_plt_id = 581

Answer 3

in this table are two columns with ID's. I want to make one of the columns distinct, and once it is distinct to select all of those from the second column of a certain ID.

SELECT distinct tableX.ID2
FROM tableX
WHERE tableX.ID1 = 581

I think your understanding of distinct may be different from how it works. This will indeed apply the where clause first, and then get a distinct list of unique entries of tableX.ID2, which is exactly what you ask for in the first part of your question.

By making a row distinct, you're ensuring no other rows are exactly the same. You aren't making a column distinct. Let's say your table has this data:

ID1   ID2
10    4
10    3
10    7
4     6

When you select distinct ID1,ID2 - you get the same as select * because the rows are already distinct.

Can you add information to clear up what you are trying to do?