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
WHERE prod_id IN (select DISTINCT prod_id from my_table
where user_id = 207)
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.
if that's what the table looks like, what is the output of the query you're expecting?
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.