Selecting Distinct on 1 field

[cafrow]

I am not sure what i am doing wrong here. I am trying to pull some records from a table. I want to return a bunch of fields, but for now lets just say I wanna return 'Color', 'Size', 'Price', 'Model', but i want to put the DISTINCT on 'Model' only so that i do not get multiple results from the same model. Here is an example of my table

Color | Size | Price | Model
Red | LG | 2.99 | 1500
Red | SM | 2.99 | 1500
Red | MD | 2.99 | 1500
Red | LG | 2.99 | 1600
Red | SM | 2.99 | 1600
Red | MD | 2.99 | 1600

I would like to retreive just 2 returns, one from 1500 and one from 1600. How would I do this, when I run this query I get all 6 returns

SELECT DISTINCT model, price, size, color FROM products

Is there a way to specify which Field you want the Distinct to apply to?

Thank you.

[chazzy]

that doesn't make sense to be honest. There has to be some other guiding information that determines that you only want 1 from each AND which one of those you want. IE do you only want the LG sizes? MD? SM? what makes those two unique?

[cafrow]

The reason this does not make sence is because a non-programming client is telling me how he wants he database to look, so when i told him we where going to have to put products in one table and the product options in another table and link them he told me NO and wants all that info in one table. So even though this database has 16,000 products it should only display 900 of them because the rest are size and color combos. I know this is a stupid way of doing it, but my client wants it this way and he pays the bill. I am thinking I just need to select DISTINCT on the model and then grab 1 entry to get the model's name and description. Then later I have to select all the colors for that model, the after they select the color I have to select all the sizes for that model in that color. (Man this is screwed up).

Anyways if there is a way to select DISTINCT on just 1 field and still retrieve other fields from that entry i would love to hear how to do that.

Thanks

[cafrow]

well I just asked another programmer that I work with who has been working with SQL longer then I have and he says he cannot think of a way to do it with 1 query, I ended up querying DISTINCT on Model then looking through on a second query and grabbing the rest of the info that I needed.

[chazzy]

The reason this does not make sence is because a non-programming client is telling me how he wants he database to look, so when i told him we where going to have to put products in one table and the product options in another table and link them he told me NO and wants all that info in one table. So even though this database has 16,000 products it should only display 900 of them because the rest are size and color combos. I know this is a stupid way of doing it, but my client wants it this way and he pays the bill. I am thinking I just need to select DISTINCT on the model and then grab 1 entry to get the model's name and description. Then later I have to select all the colors for that model, the after they select the color I have to select all the sizes for that model in that color. (Man this is screwed up).

Anyways if there is a way to select DISTINCT on just 1 field and still retrieve other fields from that entry i would love to hear how to do that.

Thanks

I would say that's your first mistake - you're designing a tool for your client, you need to show them how to best design it. Instead you're allowing them to walk all over you, thus creating a subpar product.

[cafrow]

I would say that's your first mistake - you're designing a tool for your client, you need to show them how to best design it. Instead you're allowing them to walk all over you, thus creating a subpar product.

I totally wish i could show him this post. This guy is on such a power trip its on even funny, I totally agree with you, but if i lose this account then I am not only hurting my own paycheck but the rest of my company. I have explained multiple times why not to go this way and he does not care, he had a vision and thats the way it will get done he does not care who does it. So oh well.

[chazzy]

What kind of organization do you have? Do you have specific client managers/project managers/program managers that do all this work professionally? The customer should only be dealing with how they want the UI to function. All of the business logic, the backend stuff, shouldn't matter to them. Sure certain things might change if they're strictly oracle, strict DB2, strictly SQL Server, but the same logic would still exist.

I have never heard of a customer saying "you have to layout the database this exact way" what do they know about IT? HOnestly, if they knew anything they probably wouldn't have had to hire you to develop this application in the first place!