SELECT Distinct

[Jeremy21b]

Is there a way to execute a sql query that uses the distinct clause on some columns but not others? For example, say I had a table with the following columns: id, name, color and size. Then I wanted to select all the distinct names in a certain color, but I also needed to retrieve the id & size. In other words I wanted to do some thing like this: select distinct(name), size, id. Unfortunately SQL doesn't support that syntax. I could loop through the recordset and do a bunch of separate SQL queries, but I'd like to do it all in one query for efficiency. Any help would be much appreciated.

 

[DreXor]

hmm .. could do something along those lines with :

select whatever,whatever from table where id in (select distinct whatever,whatever from table where conditions)



DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[Jeremy21b]

yep that should work...makes me wish I paid more attention in my database course. Thanks.

 

[Jeremy21b]

hmmm maybe that won't work. To use 'where id in(', I tried that and it said I need to use EXISTS to use a subquery.

 

[DreXor]

oops

Use IN for a list or a single field query, if you're going to use a sub query with more than one field use EXISTS

see this link for more detailed information on both query types.

http://b62.tripod.com/doc/sqltut.htm

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[Jeremy21b]

Sorry but that still wouldn't work. 'where id in(' would require the subquery to select the actual id, which is unique for every row. So distinct wouldn't be in effect. EXISTS is nothing more than a condition so that wouldn't work either.

 

[DreXor]

per chance you might need to post this in the SQL forum if you still havent found a resolution for it yet.

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[Jeremy21b]

I've given up. I'm using the ASP code to not repeat items instead...not as efficient, but it works....somewhat.

 

[HowardMarks]

Its hard to understand what logical results this type of query would produce if it worked anyway - say you have multiple entries in the database for one particular name. Your query would then return all distinct instances of that name, but what would it select as the matching color and number? It would have to make a choice from the multiple records that match that particular name, and would most likely just pick the first matching one. Why would it benefit you to know what the color for the first matching name was, and not the others? The whole point of the select distinct is that is has combined all of those records into one, so are you saying you would like to see a list of all the colors matching those names, next to just one copy of the name?

Perhaps if you explain in real terms what you are actually trying to achieve with a particular web page we could come up with a more logical solution.

Nick (Webmaster)

info@npfx.com

http://www.npfx.com

http://www.artswales.org

 

[Jeremy21b]

The color/name/size was just an example and not my particular application. Let me explain a little closer to my particular application...There is a name, size & category. Some names have more than one category but I only want to retrieve the first instance of the name & size.

 

[HowardMarks]

In a situation such as this, would it not make more sense to have one row per name, with a field for categories which contains a comma delimited list of the categories that the person is in?

Some thing like [1],[2],[3] in your categories field would mean that you wouldn't need to duplicate people's entries to put them in more than one category, getting rid of the whole problem of selecting distint names from the database.

YOu could then find all people who match a particular category id by doing something like : SELECT * FROM table WHERE categories LIKE '%[" & categoryid & "]%'", or split the category string to get an array of category ids.


Nick (Webmaster)

info@npfx.com

http://www.npfx.com

http://www.artswales.org

 

[Jeremy21b]

Actually it's more complicated than that. For the two different categories other fields would vary. Imagine they were different prices.

 

[HowardMarks]

in that case I would have another table called categories which housed the specific info for each category. Then there is no need to put details for each category into the users table.

Nick (Webmaster)

info@npfx.com

http://www.npfx.com

http://www.artswales.org

 

[Jeremy21b]

I am not looking to change my database structure. It is the way it is for certain reasons. I need help with an SQL query, not database structure. Thanks anyway.