selecting one record of many - 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

SQL Server 2000 Forums

SQL Server Development (2000)

selecting one record of many

Author

Topic

jwallz
Starting Member

14 Posts

Posted - 2005-10-22 : 08:24:05

we have a product category table holding product id and category id. Each product can have more than one category id. The information is stored in separate rows. If a product id has more than 1 category id it's listed in a separate row. I need a result set containing just 1 category id for each product id. Any category id is fine. I'd like to just take the first category id i see and move on to the next different product_id. how do i do this?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-22 : 08:31:09

Can you post same sample data.. . and the required result set for the same.. .

Complicated things can be done by simple thinking

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-22 : 08:43:14

Refer point 2 here
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail

jwallz
Starting Member

14 Posts

Posted - 2005-10-22 : 08:49:28

the table has two columns: product_id and category_id. A select * returns a result set like the following:

Product_id Category_id
41 15
41 17
41 47
41 48
41 661
41 671
42 19
42 20
42 24
42 36
42 37

I need just one category_id for a product_id. Doesn't matter which one. The first one for each product_id is fine:

Product_id Category_id
41 15
42 19

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-22 : 08:53:27

Try this

Select Product_id, Category_id from yourTable T
where Category_id=(Select Top 1 Category_id from yourTable where Product_id=T.Product_id)

Madhivanan

Failing to plan is Planning to fail

jwallz
Starting Member

14 Posts

Posted - 2005-10-22 : 09:01:06

That worked great! Thank you. Much appreciated.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-22 : 09:06:01

quote:
Originally posted by jwallz

That worked great! Thank you. Much appreciated.

Well

If you want to return only one record per product_Id then above query will work. If you want to return more than one record per product_id then you need to refer the link I provided in my first reply

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources