Printable View
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.
Sure, you can use distinct in a query. In your example:
select prod_id, count(distinct user_id), count(user_id)
from my_table
group by prod_id
would give you:
1 1 2
I don't think it will give you the result you are looking for, but then again, I can safely say I'm not sure what you are looking for.
I know what you mean Marist89
perhaps it's best if I show you
here's my query
select distinct(prod_id), prod_nm,
trans_status_cd, seek_offer, cat_id, cat_nm, user_id
from activity_tracker
where user_id = 207
and trans_status_cd <> 'T'
here are my results I want to get rid i only want one prod 634
can it be done??
PROD_ID PROD_NM TRANS_STAT S CAT_ID CAT_NM USER_ID
---------- ------------------------------ ---------- - ---------- ---------------------------------------- ----------
-1 null null S 421 PlayStation 207
622 Mattel Electronic Football 2 TRANSACTED S 439 Handhelds 207
629 Oracle Rocks ACTIVE O 423 PlayStation 2 207
634 Madeline Albright ACTIVE S 426 N64 207
634 Madeline Albright ACTIVE S 426 null 207
634 Madeline Albright TRANSACTED S 426 N64 207
650 test101 ACTIVE O 421 PlayStation 207
655 madden football for PS2 ACTIVE O 423 PlayStation 2 207
661 Mr. T Goes to School TRANSACTED O 426 N64 207
9 rows selected.
If you want only one row returned for prod_id 634, you can't select the columns which are different, because those three rows ARE distinct, and that's what your query is asking for.
The first two rows returned have a differnet cat_nm. The third has a different trans_status.
What is the actual data you're looking for in english? For example "I want to find all the products tracked by a user that have a transaction status of ACTIVE", or "I want to find all the products tracked by a user", etc.
Asuming the trans_status_cd makes the record unique, you might try something along the lines of:
select prod_id, prod_nm, trans_status_cd,
seek_offer, cat_id, cat_nm, user_id
from activity_tracker
where (prod_id, prod_nm, trans_status_cd,
seek_offer, cat_id, cat_nm, user_id )
in (
select prod_id, prod_nm, max(trans_status_cd)
from activity_tracker
where user_id = 207
and trans_status_cd <> 'T'
group by prod_id, prod_nm
)
and where user_id = 207
and trans_status_cd <> 'T'
However, this is only a hack. Your fundemental problem lies in the fact that the relational design is kinda out of whack. In the relational world, you should have a table of products (prod_id, prod_nm) and then only the prod_id should be stored in activity_tracker.
"I wanted to find all products tracked by a user"
then why not just use
select distinct prod_id, user_id
from activity_tracker
where user_id = 207
however as an added thought, it seems like your database layout isn't very normalized. if you normalize it you won't have to rely on DISTINCT queries (which slow down performance) and will generally make things easier to organize.
Arrrrr. (That's what happens when you assume).
In your example, cat_nm can be either N64 or null for the same prod_id?
i think my big problem is that this table is a table that a developer added to make his coding easier. Ugh! But truth be told our db is not what Dr. Codd had in mind. I'm not sure what's going on with that cat_nm - that shouldn't change. The only col that should change is trans_status_cd.