| View previous topic :: View next topic |
| Author |
Message |
cp
Beginner
Joined: 21 Oct 2005
Posts: 6
Topics: 1
|
 Posted: Fri Oct 21, 2005 3:51 am Post subject: SQL query |
 |
|
Hi ,
Can you please guide me to write SQL query for the requirement given below.FYI we are using
DB2 V7.
Table_1 ---------------- Table_2
S.No --- Cust_ID_1 -- Cust_ID_2 Product_type
1 --------- 10 ------------- 10 ---------- P1
1 --------- 20 ------------- 10 ---------- P2
1 --------- 30 ------------- 10 ---------- P3
2 --------- 10 ------------- 20 ---------- P2
2 --------- 20 ------------- 30 ---------- P3
3 --------- 10 ------------- 40 ---------- P3
3 --------- 30
3 --------- 40
Requirement: From the above tables based on matching Customer_IDs,we need to select Product_type which repeated more no:of times than any other selected for given S.No.Example, for S.no equal to 1 the o/p should be P2(this is the one repeated repeated more no:of times)
The output should be like,
S.No Product_type
1 P2
2 P2
3 P3
If you have any doubts,feel free to ask.
Thank U and good luck. |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Fri Oct 21, 2005 7:14 am Post subject: |
|
|
| Quote: |
If you have any doubts,feel free to ask.
Thank U and good luck.
|
Are you testing us?
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
cp
Beginner
Joined: 21 Oct 2005
Posts: 6
Topics: 1
|
| Posted: Mon Oct 24, 2005 4:41 am Post subject: |
|
|
Hi Kolusu...
We have experience of always less than you and not in a position to test your skills.If my words implied that we are trying test you people then I am very sorry to frame like that.
Thank U. |
|
| Back to top |
|
|
chandrankk
Beginner
Joined: 06 Dec 2005
Posts: 8
Topics: 0
|
| Posted: Tue Dec 06, 2005 8:30 am Post subject: |
|
|
you do a join on the 2 tables using customer id and then select only the distinct serial no and product type
regards |
|
| Back to top |
|
|
chandrankk
Beginner
Joined: 06 Dec 2005
Posts: 8
Topics: 0
|
| Posted: Thu Dec 15, 2005 9:07 am Post subject: |
|
|
I think the below query will do. (Got a chance to think for a while after a long time. Sorry for the wrong answer last time)
| Code: |
Select X.SNo,
X.Product
From
(
select SNo,
Product,
count(*) as count1
From Table1 A, Table2 B
Where A.Cust_id_1 = B.Cust_id_2
Group By SNo,
Product
) X,
(
Select SNo,
Max(count2) as count3
From
(
select SNo,
Product,
count(*) as count2
From Table1 A, Table2 B
Where A.Cust_id_1 = B.Cust_id_2
Group By SNo,
Product
) M
group by M.SNo
) Y
|
WHERE X.SNo = Y.SNo
AND X.count1 = Y.count3
Note that if you have both P1 and P2 with the same number of occurances, then both will come in the final result. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
