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.

 

[donutman]

There is a reason it can't be done...the Distinct clause is intended to only give you 1 row for the Distinct(Name) and you are asking for all the ID's associated with that Name.
You are really only asking for a sorted list.

SELECT Name, Size, ID FROM YourTable ORDER BY Name

You may want additional sort columns?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

I'm assuming you only want one record per Name? In this case which id and size do you want to display if there are multiple records? Perhaps you should show us some sample dat and sample results.

Questions about posting. See faq183-874

 

[Jeremy21b]

The example I gave was a little vague and not my exact scenario. Here is a better explanation....There is name, size & category. I want to get certain distinct name & sizes, but I only want to return the first category. The second category (if it exists) is somewhat generic. If it makes it any easier there is only one specific category that I want to hide...but only if the same name & size exists as a different category name too.

I was thinking there was probably a way to do it using Table joining or a Union. Now that I typed this out an Exists clause might be my solution....
(category = genericcategory AND NOT EXISTS IN (SELECT .... WHERE Category <> genericcategory))
I think I'm onto the solution, but the gears are getting jammed.

 

[donutman]

It would certainly help us and it might help you, if you gave us an example of existing data and desired output.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Jeremy21b]

No the code is really too complicated to help much. I would also prefer our website to remain anonymous.

Here is some existing data for a similar situation:
name: product1
size: small
category: menswear

name: product1
size: small
category: clearance

Then I wanted to get the distinct names & sizes, but also retrieve the 1st category. Again this is not my exact scenario, but it is enough to get the picture.

 

[donutman]

So what's the output for this example?
-Karl

 

[Jeremy21b]

output: product1 - small - menswear
but it would be done on a larger scale querying dozens of records.

 

[donutman]

How's does your example demonstrate the concept of "hiding" certain categories? Or is the clearance category always "hidden"? I'm not clairvoyant.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]