USING DISTINCT WITH COLUMN NOT ROW - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

Thread: USING DISTINCT WITH COLUMN NOT ROW

  1. #11
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095

    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.

  2. #12
    Join Date
    Aug 2000
    Posts
    132
    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.


  3. #13
    Join Date
    Jun 2000
    Posts
    417
    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.

  4. #14
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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.

  5. #15
    Join Date
    Aug 2000
    Posts
    132
    "I wanted to find all products tracked by a user"

  6. #16
    Join Date
    Jun 2000
    Posts
    417
    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.

  7. #17
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095


    Arrrrr. (That's what happens when you assume).

    In your example, cat_nm can be either N64 or null for the same prod_id?

  8. #18
    Join Date
    Aug 2000
    Posts
    132
    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
  •  



Click Here to Expand Forum to Full Width