-
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.
-
Dunno if this might work, but try:
select distinct prod_id, trans_status from my_table where user_id=207;
-
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.
-
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?
-
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)
-
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?
-
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.
-
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?
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.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|