| Author |
 Topic  |
|
|
treeskin
Starting Member
22 Posts |
 Posted - 02/08/2010 : 21:31:27
|
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
|
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 |
 |
|
|
treeskin
Starting Member
22 Posts |
Posted - 02/09/2010 : 20:12:45
|
Hi haroon2k9,
Thanks. Your query just work partially because it returns the non-duplicate as well.
Any guide for #1?
Thanks again. |
|
|
|
haroon2k9
Constraint Violating Yak Guru
328 Posts |
Posted - 02/10/2010 : 00:46:53
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 02/10/2010 : 01:21:48
|
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 |
|
|
|
haroon2k9
Constraint Violating Yak Guru
328 Posts |
Posted - 02/10/2010 : 01:38:13
|
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
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 02/10/2010 : 01:43:01
|
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 |
|
|
|
haroon2k9
Constraint Violating Yak Guru
328 Posts |
Posted - 02/10/2010 : 01:52:40
|
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.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 02/10/2010 : 01:54:33
|
No problem
You're welcome 
------------------------------------------------------------------------------------------------------
SQL Server MVP |
|
|
|
haroon2k9
Constraint Violating Yak Guru
328 Posts |
Posted - 02/10/2010 : 02:01:24
|
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.. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 02/10/2010 : 02:32:53
|
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 |
|
|
|
haroon2k9
Constraint Violating Yak Guru
328 Posts |
Posted - 02/10/2010 : 03:21:04
|
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!!! |
|
|
| |
Topic |
|
|
|
how to pull the count
