Query: select distinct problem

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

SQL Server 2000 Forums

SQL Server Development (2000)

Query: select distinct problem

Author

Topic

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2006-02-14 : 14:51:29

The query below returns multiple records for a particular product id. this is because the featured order can differ in this table. is there a way that i can only return 1 per product? ex:

PRODUCTID,ORDERITEMID,DISPLAYNAME,ACTIVE,FEATUREDORDER
1,someproduct,Some Product,1,2
1,someproduct,Some Product,1,6
2,diffproduct,Diff Product,1,1

i want to only return unique product id's. this would be simple if i didn't have to return the featuredorder, but that's required.


select distinct 
	P.ProductId, 
	P.OrderItemId, 
	P.DisplayName, 
	P.Active, 
	PDC.FeaturedOrder
from	t_ProductDP_Console PDC
	inner join t_Product P on PDC.ProductId = P.ProductId
order by PDC.FeaturedOrder

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-14 : 15:05:08

Use GROUP BY, aggregate on FeatureOrder.

GROUP BY ProductID, OrderItemID, DisplayName, Active

Tara Kizer
aka tduggan

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2006-02-14 : 15:16:47

i tried the following, but i get an error "Column name 'PDC.FeaturedOrder' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause."


select
	P.ProductId, 
	P.OrderItemId, 
	P.DisplayName, 
	P.Active
from	t_ProductDP_Console PDC
	inner join t_Product P on PDC.ProductId = P.ProductId
group by P.ProductId, P.OrderItemId, P.DisplayName, P.Active
order by PDC.FeaturedOrder

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-14 : 15:18:55

You didn't put it in an aggregate function.


select
	P.ProductId, 
	P.OrderItemId, 
	P.DisplayName, 
	P.Active,
	PDC.MIN(FeaturedOrder) AS FeaturedOrder
from	t_ProductDP_Console PDC
	inner join t_Product P on PDC.ProductId = P.ProductId
group by P.ProductId, P.OrderItemId, P.DisplayName, P.Active
order by PDC.FeaturedOrder

Tara Kizer
aka tduggan

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2006-02-14 : 15:43:31

ok that works, thanks very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources