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

Thread: USING DISTINCT WITH COLUMN NOT ROW

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

  2. #2
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    Dunno if this might work, but try:
    select distinct prod_id, trans_status from my_table where user_id=207;

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

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

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

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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?

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

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



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

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



Click Here to Expand Forum to Full Width