USING DISTINCT WITH COLUMN NOT ROW

[sukimac]

Does anyone know how to bring back rows that have distinct column values. Using the DISTINCT function was bringing back DISTINCT rows. I thought a subquery would work but it's not doing the trick. Here's my sql

select prod_id, trans_status
from my_table
WHERE prod_id IN (select DISTINCT prod_id from my_table
where user_id = 207)

Any thoughts? Thanks.

[Halo]

Dunno if this might work, but try:
select distinct prod_id, trans_status from my_table where user_id=207;

[sukimac]

yeah, that's how I originally wrote the query but when the trans_status changed it would bring back 2 rows for the same prod_id.

[pwoneill]

are you updating the status or inserting another row with a different status?

from what I can tell you have 2 rows with user_id = 207 and the same prod_id but 2 different statuses, is that how it's supposed to be working?

[sukimac]

yes pwoneill trans_status is being updated so DISTINCT is working properly b/c they are no longer DISTINCT rows but what I want to do is bring back rows based on DISTINCT column value (here prod_id)

[marist89]

If my_table looks like this:

user_id prod_id trans_status
-------- --------- ---------------
207 1 Y
207 1 N

Exactly what result are you tying to get?

[pwoneill]

marist89 spelled it out well. from what you're telling us that is what your table looks like in general.

if you issued

select distinct prod_id from my_table;

prod_id 1 would only show up one time.

however if you have 2 rows like the example above, even if you specified

select distinct prod_id trans_status where user_id = 207 and prod_id = 1

you will still get 2 rows because they are distinct, but happen to have the same prod_id.

[sukimac]

where my_table looks like this: (plus a few other cols )

user_id prod_id trans_status
-------- --------- ---------------
207 1 Y
207 1 N

Exactly what result are you tying to get?

I want 207 to return just once regardless of trans_status (which has many different states)

I want the select query is bring me back distinct products belonging to a particular user, the table is a product tracker so there can many rows with the same prod_id belonging to a particular user_id.

[pwoneill]

if that's what the table looks like, what is the output of the query you're expecting?

just

207 1

?

then just select distinct user_id, prod_id from my_table will return the correct output. as soon as you try to query trans_status you will get multiple rows because they are all distinct, because trans_status is different.

just out of curiosity, i suppose you have some means of determining which state the product being tracked is in? just from those three columns it's not distinguishable and someone wouldn't know if trans_state was supposed to be Y or N for user 203 tracking product 1.

[sukimac]

I know I'm just wondering is there is anyway to use DISTINCT on a column not a row. It seems like a subquery should work but I've been fooling with it with little success.