CODEWITHSUNDEEP
mysql
puttputt
puttputt

Reputation: 1269

Distinct Help

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 |

Upvotes: 0

Views: 215

Answers (3)

Kieveli
Kieveli

Reputation: 11075

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?

Upvotes: 0

Jon Erickson
Jon Erickson

Reputation: 114826

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

Upvotes: 1

Alex Martelli
Alex Martelli

Reputation: 881537

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;

Upvotes: 2

Related Questions