04-13-2004, 12:52 AM
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.
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.
04-13-2004, 05:03 AM
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.
How should be decided which record to return (whic of the roles?)
person_id item_id roles
5 5 'designer, artist'