SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 how to pull the count
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

treeskin
Starting Member

22 Posts

Posted - 02/08/2010 :  21:31:27  Show Profile  Reply with Quote
Hello Experts,

Need your help on below.

1) How to write a query to pull for product from the same supplier for which the product code could have different from each other? See below example result:

Product Code Product Description Supplier
1234 Pencil Supplier A
3456 Pencil Supplier A
5678 Rubber Supplier B
9870 Rubber Supplier B


2) How to write a query to pull a product list for which the different product having the same product code? See below example result:
Product Code Product Description Supplier
1234 Pencil Supplier A
1234 Rubber Supplier B
7890 A4 Paper Supplier A
7890 Ruler - 12cm Supplier B


Thank you :-)

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 02/08/2010 :  23:34:07  Show Profile  Reply with Quote
Iam assuming you are trying to find out the Duplicate Product code.
select Pcode,product,description,suplier from
(
select rownum=Row_Number()over(partition by [produt code] order by [produt code]),[Product code]Pcode,product,description,suplier from tbl
)t
where t.rownum > 1
Go to Top of Page

treeskin
Starting Member

22 Posts

Posted - 02/09/2010 :  20:12:45  Show Profile  Reply with Quote
Hi haroon2k9,

Thanks. Your query just work partially because it returns the non-duplicate as well.

Any guide for #1?

Thanks again.
Go to Top of Page

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 02/10/2010 :  00:46:53  Show Profile  Reply with Quote
1.
Product Code Product Description Supplier
1234 Pencil Supplier A
3456 Pencil Supplier A
5678 Rubber Supplier B
9870 Rubber Supplier B

SELECT [PRODUCT COE],PRODUCT,DESCRIPTION,SUPPLIER
(
SELECT rownum=ROW_NUMBER()OVER(PARTITION BY PRODUCT,SUPPLIER ORDER BY PRODUCTCODE),[PRODUT CODE],PRODUCT,DESCRIPTION,SUPPLIER FROM TBL)
)T WHERE T.rownum=1 ORDER BY PRODUCTCODE

2.

Product Code Product Description Supplier
1234 Pencil Supplier A
1234 Rubber Supplier B
7890 A4 Paper Supplier A
7890 Ruler - 12cm Supplier B

SELECT [PRODUCT COE],PRODUCT,DESCRIPTION,SUPPLIER
(
SELECT rownum=ROW_NUMBER()OVER(PARTITION BY [PRODUCT CODE],PRODUCT ORDER BY PRODUCTCODE),[PRODUCT CODE],PRODUCT,DESCRIPTION,SUPPLIER FROM TBL)
)T WHERE T.rownum > 1 ORDER BY PRODUCTCODE

Could pls check and let me know
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/10/2010 :  01:21:48  Show Profile  Reply with Quote
quote:
Originally posted by haroon2k9

1.
Product Code Product Description Supplier
1234 Pencil Supplier A
3456 Pencil Supplier A
5678 Rubber Supplier B
9870 Rubber Supplier B

SELECT [PRODUCT COE],PRODUCT,DESCRIPTION,SUPPLIER
(
SELECT rownum=ROW_NUMBER()OVER(PARTITION BY PRODUCT,SUPPLIER ORDER BY PRODUCTCODE),[PRODUT CODE],PRODUCT,DESCRIPTION,SUPPLIER FROM TBL)
)T WHERE T.rownum=1 ORDER BY PRODUCTCODE

2.

Product Code Product Description Supplier
1234 Pencil Supplier A
1234 Rubber Supplier B
7890 A4 Paper Supplier A
7890 Ruler - 12cm Supplier B

SELECT [PRODUCT COE],PRODUCT,DESCRIPTION,SUPPLIER
(
SELECT rownum=ROW_NUMBER()OVER(PARTITION BY [PRODUCT CODE],PRODUCT ORDER BY PRODUCTCODE),[PRODUCT CODE],PRODUCT,DESCRIPTION,SUPPLIER FROM TBL)
)T WHERE T.rownum > 1 ORDER BY PRODUCTCODE

Could pls check and let me know



------------------------------------------------------------------------------------------------------
SQL Server MVP
Go to Top of Page

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 02/10/2010 :  01:38:13  Show Profile  Reply with Quote
Hi visakh16,

i dint get anything by this ..

quote:
Originally posted by visakh16

quote:
Originally posted by haroon2k9

1.
Product Code Product Description Supplier
1234 Pencil Supplier A
3456 Pencil Supplier A
5678 Rubber Supplier B
9870 Rubber Supplier B

SELECT [PRODUCT COE],PRODUCT,DESCRIPTION,SUPPLIER
(
SELECT rownum=ROW_NUMBER()OVER(PARTITION BY PRODUCT,SUPPLIER ORDER BY PRODUCTCODE),[PRODUT CODE],PRODUCT,DESCRIPTION,SUPPLIER FROM TBL)
)T WHERE T.rownum=1 ORDER BY PRODUCTCODE

2.

Product Code Product Description Supplier
1234 Pencil Supplier A
1234 Rubber Supplier B
7890 A4 Paper Supplier A
7890 Ruler - 12cm Supplier B

SELECT [PRODUCT COE],PRODUCT,DESCRIPTION,SUPPLIER
(
SELECT rownum=ROW_NUMBER()OVER(PARTITION BY [PRODUCT CODE],PRODUCT ORDER BY PRODUCTCODE),[PRODUCT CODE],PRODUCT,DESCRIPTION,SUPPLIER FROM TBL)
)T WHERE T.rownum > 1 ORDER BY PRODUCTCODE

Could pls check and let me know



------------------------------------------------------------------------------------------------------
SQL Server MVP

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/10/2010 :  01:43:01  Show Profile  Reply with Quote
quote:
Originally posted by haroon2k9

Hi visakh16,

i dint get anything by this ..

quote:
Originally posted by visakh16

quote:
Originally posted by haroon2k9

1.
Product Code Product Description Supplier
1234 Pencil Supplier A
3456 Pencil Supplier A
5678 Rubber Supplier B
9870 Rubber Supplier B

SELECT [PRODUCT COE],PRODUCT,DESCRIPTION,SUPPLIER
FROM
(
SELECT rownum=ROW_NUMBER()OVER(PARTITION BY PRODUCT,SUPPLIER ORDER BY PRODUCTCODE),[PRODUT CODE],PRODUCT,DESCRIPTION,SUPPLIER FROM TBL)
)T WHERE T.rownum=1 ORDER BY PRODUCTCODE

2.

Product Code Product Description Supplier
1234 Pencil Supplier A
1234 Rubber Supplier B
7890 A4 Paper Supplier A
7890 Ruler - 12cm Supplier B

SELECT [PRODUCT COE],PRODUCT,DESCRIPTION,SUPPLIER
FROM
(
SELECT rownum=ROW_NUMBER()OVER(PARTITION BY [PRODUCT CODE],PRODUCT ORDER BY PRODUCTCODE),[PRODUCT CODE],PRODUCT,DESCRIPTION,SUPPLIER FROM TBL)
)T WHERE T.rownum > 1 ORDER BY PRODUCTCODE

Could pls check and let me know



------------------------------------------------------------------------------------------------------
SQL Server MVP




you had some unwanted brackets so i tidied it up.
Just noticed you've left out FROM also
Could you please test your solutions before posting?

------------------------------------------------------------------------------------------------------
SQL Server MVP

Edited by - visakh16 on 02/10/2010 01:43:22
Go to Top of Page

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 02/10/2010 :  01:52:40  Show Profile  Reply with Quote
Oh!.iam extremy sorry for that.
quote:

you had some unwanted brackets so i tidied it up.
Just noticed you've left out FROM also



sure.
quote:

Could you please test your solutions before posting?



Thank you very much visakh16.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/10/2010 :  01:54:33  Show Profile  Reply with Quote
No problem
You're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
Go to Top of Page

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 02/10/2010 :  02:01:24  Show Profile  Reply with Quote
I would like to thank you visakh16.you are also one of my sql guru here.actually the use of partition i learnt from u and here(sqlteam forum)..iam also just a beginner of sql.i started learning it here only.
i would like to thanks for everyone(u,kristen,madhivanan..etc) who is contributing answers are very excellent..because of this iam a beginner of this sql,started contributing my answers here.so all my gredits goes here..

Thanks again for everyone contributing this forum..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/10/2010 :  02:32:53  Show Profile  Reply with Quote
quote:
Originally posted by haroon2k9

I would like to thank you visakh16.you are also one of my sql guru here.actually the use of partition i learnt from u and here(sqlteam forum)..iam also just a beginner of sql.i started learning it here only.
i would like to thanks for everyone(u,kristen,madhivanan..etc) who is contributing answers are very excellent..because of this iam a beginner of this sql,started contributing my answers here.so all my gredits goes here..

Thanks again for everyone contributing this forum..


You're welcome
Glad that I could help out so much in your learnings
Follow this forum closely and you will get opportunity to learn a lot, thanks to all the great gurus who participate here

------------------------------------------------------------------------------------------------------
SQL Server MVP
Go to Top of Page

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 02/10/2010 :  03:21:04  Show Profile  Reply with Quote
quote:

You're welcome
Glad that I could help out so much in your learnings
Follow this forum closely and you will get opportunity to learn a lot, thanks to all the great gurus who participate here




Definitely!!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000