|
View previous topic :: View next topic
|
| Author |
Message |
r arunmoezhi
New User
Joined: 26 Jun 2009
Posts: 32
Location: Chennai
|
|
|
|
there are two columns in a table
| Code: |
COL A COL B
CF C2
CF C4
CF D9
DH D8
DH 9F
DH 9Z
Z1 C2
Z1 C4
Z1 D9
Z1 GN
Z1 3Z
|
My requirement is as follows:
Any value (eg: D9) from COL B would be specified in the query.
For the specified value the corresponding values in COL A should be obtained.
(eg: D9 in COL B would fetch CF and Z1 from COL A)
| Code: |
SELECT COL_A FROM TABLE
WHERE COL_B = 'D9'
|
Now we have a list of values from COL A. For this list I need to fetch all the values from COL B.
Eg:
for CF and Z1 from COL A the result set would be
| Code: |
COL A COL B
CF C2
CF C4
CF D9
Z1 C2
Z1 C4
Z1 D9
Z1 GN
Z1 3Z
|
| Code: |
SELECT COL_A, COL_B FROM TABLE
WHERE COL_A IN
(
SELECT COL_A FROM TABLE
WHERE COL_B = 'D9'
)
|
I need the above result to be ORDERED based on number of rows for each value in COL A.
Eg:
Since Z1 has 5 records and CF has 3 records the result should be reordered as
| Code: |
COL A COL B
Z1 C2
Z1 C4
Z1 D9
Z1 GN
Z1 3Z
CF C2
CF C4
CF D9
|
|
|
| Back to top |
|
 |
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| Code: |
SELECT A.C1,A.C2,FROM
(SELECT C1,C2 FROM
TABLE
WHERE C1 IN
(SELECT C1 FROM TABLE
WHERE C2 ='C4')) AS A
,
(SELECT C1,COUNT(*) AS COUNT FROM TABLE GROUP BY C1) AS B
WHERE A.C1 = B.C1
ORDER BY B.COUNT DESC ;
|
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Code: |
SELECT b.ColA,b.ColB, count(*) FROM TAB1 a
join TAB1 b on b.ColA = a.ColA
join TAB1 c on c.ColA = c.ColA
WHERE a.ColB ='D9'
group by b.ColA,b.ColB
order by 3 desc
|
|
|
| Back to top |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007
Posts: 295
Location: Singapore
|
|
|
|
Guyc,
Believe it should be
| Code: |
| join TAB1 c on c.ColA = b.ColA |
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Srihari Gonugunta wrote: |
Guyc,
Believe it should be
| Code: |
| join TAB1 c on c.ColA = b.ColA |
|
oops, yes of course |
|
| Back to top |
|
|
|
|
