Home » SQL & PL/SQL » SQL & PL/SQL » I Need Select Query....
I Need Select Query.... [message #341724] Wed, 20 August 2008 02:13 Go to next message
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 #341742 is a reply to message #341724] Wed, 20 August 2008 02:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use WHERE clause.

Regards
Michel
Re: I Need Select Query.... [message #341751 is a reply to message #341724] Wed, 20 August 2008 03:26 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: I Need Select Query.... [message #342025 is a reply to message #342001] Thu, 21 August 2008 01:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And BEFORE posting, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: I Need Select Query.... [message #342049 is a reply to message #342001] Thu, 21 August 2008 03:14 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
If you don't mind could you share with us the version of oracle you are using ?

Regards

Raj
Previous Topic: rownum gives no data found!!!!!!!!!!!!111
Next Topic: Create a trigger in a procedure
Goto Forum:
  


Current Time: Fri Dec 09 06:01:15 CST 2016

Total time taken to generate the page: 0.11447 seconds