Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
token
Posting Yak Master
133 Posts |
 Posted - 2007-01-01 : 09:10:13
|
Hi all. Happy New Year 2007!!!I have a problem which I cannot find a solution to no matter how hard I try.I am trying to retrieve records about products from a table. But I only want to return 1 row of each product, hence I used the DISTINCT word. But I also want to return an image for each DISTINCT product. However there are many images associated with each distinct product, and as you know the DISTINCT function only returns distinct combinations of colums. So I have a problem where I am having the product repeat itself on my webpage because there are different images available to be shown.This is my SQL code at the moment:---SELECT DISTINCT p1.Manufacturer_Family_Member,p1.Manufacturer_Family,p1.Manufacturer, p1.Image,fmd1.DescriptionFROM dbo.StageJOIN dbo.Products p1 ON p1.Partno = dbo.Stage.ManfProductCodeJOIN dbo.FamilyMemberDescs fmd1 ON fmd1.Manufacturer_Family_Member = p1.Manufacturer_Family_MemberWHERE (p1.CategoryID = #URL.Categories#) AND (p1.Manufacturer LIKE '%#URL.Manufacturers#%') ORDER BY p1.Manufacturer_Family_Member asc---So basically I am getting DISTINCT combinations Manufacturers and Manufacter_Family, Manufacturer_Family_Member. These three translate to something like Sony, C Series, 15.4". So this means that 'Sony' is the Manufacturer, 'C Series' is the Family, and '15.4"' is the Member of that Family.So far so good. But 'Sony, C Series, 15.4"' returns many images associated with it in the Image colum. I only want to get 1 image, maybe just the first 1 that is retrieved.Any idea how to do this? I am so stuck!  |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-01 : 09:37:18
|
| You must define which is "one of each product".Smallest product? Most valuable? Largest? Cheapest? Most bought? Newest? The most invisible?Peter LarssonHelsingborg, Sweden |
 |
|
|
token
Posting Yak Master
133 Posts |
Posted - 2007-01-01 : 13:55:48
|
| Any random one will be fine. There is no way to actually define a unique property. The only unique element to a product is its Part Number.The results of my SQL query in my previous posts returns results like this:Acer | Aspire | 5100WLMi | 111.jpgSony | C Series | 15.4" | 123.jpgSony | C Series | 15.4" | 124.jpgSony | C Series | 15.4" | 125.jpgWhat I have done is create 1 webpage which shows products by their Manufacturer_Family and Manufacturer_Family_Member.The 2nd webpage then shows each individual model of that Manufacturer_Family_Member by its Part No (the first column).So what I am trying to achieve is that the 1st webpage would only show the 1st row of each DISTINCT combination of Manufacturer_Family and Manufacturer_Family_Member. So first result in the the 'Sony, C Series 15.4"' should be displayed. When the user clicks on it (hyperlink), he will then go to the 2nd webpage where all the models are shown.Currently what is happening is that the webpage is returning 3 results on the 1st page that says 'Sony, C Series, 15.4"' because there are 3 images for it. If it just chooses 1 image, then it should only display 1 result for the Sony C Series 15.4" family.Any suggestions? I looked into SELECT TOP 1 statement but this didnt seem to work. But maybe I was doing it wrong? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-01 : 14:21:11
|
| using your sample data aboveselect col1, col2, col3, max(col4)from <YourTableNameHere>group by col1, col2, col3order by col1, col2, col3Peter LarssonHelsingborg, Sweden |
|
|
|
token
Posting Yak Master
133 Posts |
Posted - 2007-01-01 : 14:52:57
|
| It worked! It was the MAX statement for the Image column that basically did the trick. Many thanks Peter, you have really helped me. Wish you a very happy and healthy new year!!! |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
Limiting rows returned