Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Nov 2002
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SELECT DISTINCT Problem

    Hi there,
    I am trying to do a SQL Query where I want to select data from all the table colums (SELECT * FROM TABLE) but only where one of the colums is DISTINCT... any ideas.. basically I want this:

    SELECT col1, DISTINCT col2 WHERE id=5

    Is this possible? Do I make any sense.

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    no it's not possible. Just try to write such a recordset on paper , and you'll see that each field for each record needs to have a value. So either all variables or none of then get agregated. So you need to use a
    SELECT DISTINCT col1, col2 WHERE id=5

    But maybe give us the info for your concrete task, and we might have a sollution.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #3
    New Coder
    Join Date
    Nov 2002
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the response, here is what I am trying to accomplish. I have a table with these fields, id (key), person_id, item_id, role.

    person_id is linked to another table that contains more info about the person

    I need to select all the records in the table with a unique person_id where the item_id is a certain value so...:

    SELECT DISTICT person_id WHERE item_id=5

    However I also need one of the roles with each of these records, and the roles are all different for each record.. so there may be a entry like this:

    person_id item_id role
    5 5 designer
    5 5 artist

    All I want is one of these records.. but there will be all sorts of different person_id's with different roles per each item_id. I could probably just select all the records and then loop them to fish out what I needed.

    Hope this makes some sense.

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How should be decided which record to return (whic of the roles?)
    Would

    person_id item_id roles
    5 5 'designer, artist'

    be acceptable?
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •