-
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 <b>PlayStation</b> 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.
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
|