I Need Select Query.... [message #341724] |
Wed, 20 August 2008 02:13  |
raajes_n
Messages: 33 Registered: April 2005 Location: India
|
Member |
 
|
|
Hi
AP_CDE FLT_Num LEG_SEQ_NUM OP_LEG_FLT_NUM OP_LEG_AL_CDE
DTW 25 -1 245 NW
DTW 25 0 245 NW
DTW 25 1 245 NW
DTW 25 -1 245 NW
DTW 25 0 11 NW
DTW 25 1 345 KL
above is my table.
I want to select only those rows whose OP_LEG_FLT_NUM, OP_LEG_AL_CDE are same for example the result shoule be as follows
AP_CDE FLT_Num LEG_SEQ_NUM OP_LEG_FLT_NUM OP_LEG_AL_CDE
DTW 25 -1 245 NW
DTW 25 0 245 NW
DTW 25 1 245 NW
Help me out
Thanks
[Mod-Edit: Frank added [code]tags to improve readability, as is recommended/requested in the Forum rules.]
Rajes
[Updated on: Wed, 20 August 2008 02:20] by Moderator Report message to a moderator
|
|
|
|
Re: I Need Select Query.... [message #341751 is a reply to message #341724] |
Wed, 20 August 2008 03:26   |
uguddu
Messages: 5 Registered: August 2008
|
Junior Member |
|
|
Hi,
try below quert..
SELECT AP_CDE,FLT_Num,LEG_SEQ_NUM,OP_LEG_FLT_NUM,OP_LEG_AL_CDE
from TABLE T1 where 1<( select count(*) from TABLE T2 where
T2.OP_LEG_FLT_NUM =T1.OP_LEG_FLT_NUM
AND T2.OP_LEG_AL_CDE=T1.OP_LEG_AL_CDE
)
ORDER BY OP_LEG_FLT_NUM,OP_LEG_AL_CDE
Regards,
Avadhesh
|
|
|
Re: I Need Select Query.... [message #341757 is a reply to message #341751] |
Wed, 20 August 2008 03:51   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
uguddu wrote on Wed, 20 August 2008 10:26 | Hi,
try below quert..
|
Did you try it yourself?
SQL> with data as
2 ( select 'DTW' AP_CDE
3 , 25 FLT_NUM
4 , -1 LEG_SEQ_NUM
5 , 245 OP_LEG_FLT_NUM
6 , 'NW' OP_LEG_AL_CDE
7 from dual
8 union all
9 select 'DTW' AP_CDE
10 , 25 FLT_NUM
11 , 0 LEG_SEQ_NUM
12 , 245 OP_LEG_FLT_NUM
13 , 'NW' OP_LEG_AL_CDE
14 from dual
15 union all
16 select 'DTW' AP_CDE
17 , 25 FLT_NUM
18 , 1 LEG_SEQ_NUM
19 , 245 OP_LEG_FLT_NUM
20 , 'NW' OP_LEG_AL_CDE
21 from dual
22 union all
23 select 'DTW' AP_CDE
24 , 25 FLT_NUM
25 , -1 LEG_SEQ_NUM
26 , 245 OP_LEG_FLT_NUM
27 , 'NW' OP_LEG_AL_CDE
28 from dual
29 union all
30 select 'DTW' AP_CDE
31 , 25 FLT_NUM
32 , 0 LEG_SEQ_NUM
33 , 11 OP_LEG_FLT_NUM
34 , 'NW' OP_LEG_AL_CDE
35 from dual
36 union all
37 select 'DTW' AP_CDE
38 , 25 FLT_NUM
39 , 1 LEG_SEQ_NUM
40 , 345 OP_LEG_FLT_NUM
41 , 'NW' OP_LEG_AL_CDE
42 from dual
43 )
44 SELECT AP_CDE
45 , FLT_Num
46 , LEG_SEQ_NUM
47 , OP_LEG_FLT_NUM
48 , OP_LEG_AL_CDE
49 from data T1 where 1 < ( select count(*)
50 from data T2
51 where T2.OP_LEG_FLT_NUM = T1.OP_LEG_FLT_NUM
52 and T2.OP_LEG_AL_CDE = T1.OP_LEG_AL_CDE
53 )
54 order by OP_LEG_FLT_NUM
55 , OP_LEG_AL_CDE;
AP_ FLT_NUM LEG_SEQ_NUM OP_LEG_FLT_NUM OP
--- ---------- ----------- -------------- --
DTW 25 -1 245 NW
DTW 25 -1 245 NW
DTW 25 1 245 NW
DTW 25 0 245 NW
|
|
|
Re: I Need Select Query.... [message #341760 is a reply to message #341757] |
Wed, 20 August 2008 03:58   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
A working solution would be to group the records, based on the two grouping columns. Calculate the rownumber within each group (row_number() over (partition by ....., .....)
and select the rows for which that row_number >= 2
|
|
|
Re: I Need Select Query.... [message #342001 is a reply to message #341757] |
Thu, 21 August 2008 01:03   |
uguddu
Messages: 5 Registered: August 2008
|
Junior Member |
|
|
SELECT AP_CDE,FLT_Num,LEG_SEQ_NUM,OP_LEG_FLT_NUM,OP_LEG_AL_CDE
from TABLE T1 where 1<( select count(*) from TABLE T2 where
T2.OP_LEG_FLT_NUM =T1.OP_LEG_FLT_NUM
AND T2.OP_LEG_AL_CDE=T1.OP_LEG_AL_CDE
)
ORDER BY OP_LEG_FLT_NUM,OP_LEG_AL_CDE
Replace TABLE by any test tablename from above query then it will give correct result
|
|
|
Re: I Need Select Query.... [message #342005 is a reply to message #342001] |
Thu, 21 August 2008 01:10   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Why don't you post your solution the way I did? Use a with-clause (or a table) to hold the data and prove that your query generates the results the op wants.
I am quite confident that yours may return duplicate rows...
In fact, I already showed you.
Reposting the same query doesn't all of a sudden make it work, I'm afraid.
|
|
|
|
|