distinct on two columns - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

distinct on two columns

Author

Topic

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-05-21 : 01:19:37

Hi all,

I have a table named products which has a foreign key categoryId. I want to show distinct categories which have products. I am joining product table with categories to get categoryname. but If a category exists more than once in products table, I need to show it only once. how can I apply distinct to product.categoryid and categories.categoryname. but it is not showing distinct.

here is my sql stored proc.

ALTER PROCEDURE [dbo].[SelectNewProductCategories]
(
@IsEnglish bit = 1
)
AS

SET NOCOUNT ON

select distinct p.catid CategoryID,
case when @IsEnglish = 1 then pc.CategoryName else pc.CategoryNamesp end as CategoryName
,p.productSmallImageURL ImageURL
from products p inner join ProductCategories pc
on p.catid = pc.CategoryID
where
p.IsActive = 1
and p.IsOutOfStock = 0
and pc.IsActive = 1
order by
p.CreatedOn,p.ModifiedOn

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-21 : 01:27:49

it is the url for each product that set things wrong for you.

E 12°55'05.63"
N 56°04'39.26"

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-05-21 : 01:31:36

Hi Peso, thanks for the response. what should be correct way to do this ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-21 : 01:43:50

It depends.
Are you satisfied with only one URL per category?

select categoryid, categoryname, min(imageurl) AS imageurl
from ( select p.catid as CategoryID,
case when @IsEnglish = 1 then pc.CategoryName else pc.CategoryNamesp end as CategoryName
,p.productSmallImageURL as ImageURL,
p.CreatedOn, p.ModifiedOn
from products as p inner join ProductCategories as pc on p.catid = pc.CategoryID
where p.IsActive = 1 and p.IsOutOfStock = 0 and pc.IsActive = 1
) AS d
group by categoryid, categoryname
order by min(CreatedOn), min(ModifiedOn)

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources