...

View Full Version : SELECT DISTINCT Problem



jeremywatco
04-12-2004, 11:52 PM
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.

raf
04-12-2004, 11:58 PM
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.

jeremywatco
04-13-2004, 04: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.

raf
04-13-2004, 07:29 AM
How should be decided which record to return (whic of the roles?)
Would

person_id item_id roles
5 5 'designer, artist'

be acceptable?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum