how to get distinct rows - 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

how to get distinct rows

Author

Topic

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-06-08 : 02:28:42

Hi All,

I have two tables product subcategories and products. each product has subcategory id as foreign key. I want to get distinct product subcategories from product table and their names from product subcategory table. But distinct is not working in follwing query

SELECT
p.[subcatId],
ps.[SubCatName],
ps.[SubCatNameSp]

FROM
[ProductSubCategories] ps inner join products p
on p.subcatid = ps.subcatid
WHERE
p.IsActive = 1
AND p.OnSale = 1
AND p.IsOutOfStock = 0
AND ps.[IsActive] = 1
-- AND ps.CategoryID = @CategoryID
group by
p.[subcatId],
ps.[SubCatName],
ps.[SubCatNameSp],
p.CreatedOn ,
p.ModifiedOn

ORDER BY
p.CreatedOn ASC,
p.ModifiedOn ASC,
ps.[SubCatName] ASC

I am getting following outout

subcatId SubCatName SubCatNameSp
----------- -------------------------------------------------- --------------------------------------------------
8 Country Caps Gorras de Paises
17 Gym Bags Bolsas de Gimnasio
1 Cotton Caps Gorras de Algodon
40 Seat Covers Cubreasientos
20 Original Jerseys Playeras Originales
20 Original Jerseys Playeras Originales

how to select one of alst two records.

Please help me on this.

Regards,
Asif Hameed

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-08 : 02:33:16

[code]
SELECT
p.[subcatId],
ps.[SubCatName],
ps.[SubCatNameSp]

FROM
[ProductSubCategories] ps
inner join products p on p.subcatid = ps.subcatid

WHERE
p.IsActive = 1
AND p.OnSale = 1
AND p.IsOutOfStock = 0
AND ps.[IsActive] = 1

group by
p.[subcatId],
ps.[SubCatName],
ps.[SubCatNameSp]

ORDER BY
max(p.CreatedOn) ASC,
max(p.ModifiedOn) ASC,
ps.[SubCatName] ASC
[/code]

KH
[spoiler]Time is always against us[/spoiler]

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-06-08 : 02:39:55

Thanks KH, it worked. Can you explain it a bit why did u use group by and also why did you apply max on CreatedOn and ModifiedOn in Order by clause

Thank you again

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-08 : 03:13:49

In your original query you are getting such result because for each combination of p.[subcatId], ps.[SubCatName], ps.[SubCatNameSp] there are several different CreatedOn and ModifiedOn, as the Created and Modified is per product.

If you don't need to order it by the CreatedOn or ModifiedOn date, you can simply change to

ORDER BY ps.[SubCatName] ASC

But since you have included it there, i figure you wanted this. But SQL Server will not allow you to ORDER BY a column that is not in the SELECT column list except aggregated column. As there will be several CreatedOn date per subcatid, applying the MAX() on the dates column, returns a single date per subcatid which will allow SQL Server to perform the ordering

the query is essentially same as


SELECT
	p.[subcatId],
	ps.[SubCatName],
	ps.[SubCatNameSp],
	max(p.CreatedOn),
	max(p.ModifiedOn)

FROM
	[ProductSubCategories] ps 
	inner join products p	on p.subcatid = ps.subcatid

WHERE
	p.IsActive = 1
AND 	p.OnSale = 1
AND 	p.IsOutOfStock = 0
AND 	ps.[IsActive] = 1

group by
	p.[subcatId],
	ps.[SubCatName],
	ps.[SubCatNameSp]

ORDER BY
	max(p.CreatedOn) ASC,
	max(p.ModifiedOn) ASC,
	ps.[SubCatName] ASC

KH
[spoiler]Time is always against us[/spoiler]

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-06-08 : 03:24:20

Hi KH, thanks for the explanation. I have tried to use same formula on below query but it is still showing duplicates. CategoryId is foreign key in products. I need distinct product categories.

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

SET NOCOUNT ON

select top 6 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
and p.isnew = 1
group by
p.catid,
pc.CategoryName,
pc.CategoryNamesp,
p.productSmallImageURL
order by
max(p.CreatedOn),
max(p.ModifiedOn)

CategoryID CategoryName ImageURL
----------- -------------------------------------------------- --------------------------------------------------
3 Bags SUNSET.JPG
2 Caps SUNSET.JPG
9 Automotive Accessories
5 Apparel YHST-7223899490465_2051_1903834512.JPG
5 Apparel POLITICS.JPG
5 Apparel
Warning: Null value is eliminated by an aggregate or other SET operation.

Thanks a lot for your time,

Regards,
Asif Hameed

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-08 : 03:34:49

[code]
select top 6
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
and p.isnew = 1
group by
p.catid,
-- use the same expression as in your select
case when @IsEnglish = 1 then pc.CategoryName else pc.CategoryNamesp end,
p.productSmallImageURL
order by
max(p.CreatedOn),
max(p.ModifiedOn)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-06-08 : 03:43:39

still showing duplicates, I get following results

CategoryID CategoryName ImageURL
----------- -------------------------------------------------- --------------------------------------------------
3 Bolsas Deportivas SUNSET.JPG
2 Gorras SUNSET.JPG
9 Accesorios Automotrices
5 Vestimentos YHST-7223899490465_2051_1903834512.JPG
5 Vestimentos POLITICS.JPG
5 Vestimentos
Warning: Null value is eliminated by an aggregate or other SET operation.

(6 row(s) affected)

I used following query:

DECLARE @IsEnglish bit
select top 6
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
and p.isnew = 1
group by
p.catid,
-- use the same expression as in your select
case when @IsEnglish = 1 then pc.CategoryName else pc.CategoryNamesp end,
p.productSmallImageURL
order by
max(p.CreatedOn),
max(p.ModifiedOn)

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-08 : 03:46:35

quote:
still showing duplicates, I get following results

CategoryID CategoryName ImageURL
----------- -------------------------------------------------- --------------------------------------------------
3 Bolsas Deportivas SUNSET.JPG
2 Gorras SUNSET.JPG
9 Accesorios Automotrices
5 Vestimentos YHST-7223899490465_2051_1903834512.JPG
5 Vestimentos POLITICS.JPG
5 Vestimentos
Warning: Null value is eliminated by an aggregate or other SET operation.

(6 row(s) affected)

Where is the duplicates ? I don't see any duplicates there

KH
[spoiler]Time is always against us[/spoiler]

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-06-08 : 03:54:16

last three rows are same. I want only one row having categoryid = 1. Basically, I need last categoryID added of three ( 5 in this case). All products have categoryid as foreign key. I want distinct and last categoryId added to produc table .. whatever is in its imageURL Although imageURL are different in above resultset. for example I want only this

5 Vestimentos YHST-7223899490465_2051_1903834512.JPG

and not these

5 Vestimentos POLITICS.JPG
5 Vestimentos

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-08 : 03:59:17

quote:
Originally posted by sparrow37

last three rows are same. I want only one row having categoryid = 1. Basically, I need last categoryID added of three ( 5 in this case). All products have categoryid as foreign key. I want distinct and last categoryId added to produc table .. whatever is in its imageURL Although imageURL are different in above resultset. for example I want only this

5 Vestimentos YHST-7223899490465_2051_1903834512.JPG

and not these

5 Vestimentos POLITICS.JPG
5 Vestimentos

that's what you get if you included the p.productSmallImageURL.

GROUP BY is a bit similar to DISTINCT. It will give you the distinct combination of columns defined in the GROUP BY column list.

which one of the productSmallImageURL that you want ? try MAX() or MIN()


select top 6 
       p.catid CategoryID,
       case when @IsEnglish = 1 then pc.CategoryName else pc.CategoryNamesp end as CategoryName,
       MAX(p.productSmallImageURL) as ImageURL

and remove productSmallImageURL from the GROUP BY clause

KH
[spoiler]Time is always against us[/spoiler]

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-06-08 : 04:05:23

Thanks KH,

It worked. by the way what does it mean

Warning: Null value is eliminated by an aggregate or other SET operation.

what to do ot eliminate this

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-08 : 04:10:11

it means you have NULL value in one of the aggregate column. Like p.productSmallImageURL or p.CreatedOn or p.ModifiedOn

It is just a warning. You can ignore it.

OR use ISNULL on the column

like

MAX(isnull(p.productSmallImageURL, ''))

MAX(isnull(p.CreatedOn, '19000101'))

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources