Home » SQL & PL/SQL » SQL & PL/SQL » Need help with SQL query (Oracle 10.1.2.0)
Need help with SQL query [message #560955] Wed, 18 July 2012 00:16 Go to next message
urnikhil
Messages: 42
Registered: March 2008
Member
Hi All,

I need help with the following sql query.

I have a table named 'x'. It has values as shown below.
COL1    COL2    COL3    COL4    COL5
----    ----    ----    ----    ----
A        1       K       1       N
A        1       K       50      J
A        1       K       99      R
B        2       J        1      N
B        2       J        5      N
C        3       M        8      N
C        3       M        99     U
D        7       R        2      L


I am trying to write a query so that if the value in col4 is "99" for any given set of col1,col2,col3 values, we have to display only that record ignoring the rest in that set. For example, when we select the values from above table, I need the result of the query to look like the following:
COL1    COL2    COL3    COL4    COL5
----    ----    ----    ----    ----
A        1       K       99      R  (eliminated two other records)
B        2       J        1      N
B        2       J        5      N
C        3       M        99     U  (eliminated one other record)
D        7       R        2      L

Please help me with the query. Thanks a lot.

- Nik.
Re: Need help with SQL query [message #560962 is a reply to message #560955] Wed, 18 July 2012 00:45 Go to previous messageGo to next message
Littlefoot
Messages: 19693
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's one option:
SQL> select * from test;

C       COL2 C       COL4 C
- ---------- - ---------- -
A          1 K          1 N
A          1 K         50 J
A          1 K         99 R
B          2 J          1 N
B          2 J          5 N
C          3 M          8 N
C          3 M         99 U
D          7 R          2 L

8 rows selected.

SQL> select * from test
  2  where col4 = 99
  3  union
  4  select * from test
  5  where (col1, col2, col3) not in (select col1, col2, col3
  6                                   from test
  7                                   where col4 = 99
  8                                  );

C       COL2 C       COL4 C
- ---------- - ---------- -
A          1 K         99 R
B          2 J          1 N
B          2 J          5 N
C          3 M         99 U
D          7 R          2 L

SQL>
Re: Need help with SQL query [message #560963 is a reply to message #560955] Wed, 18 July 2012 00:49 Go to previous message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous SQL topic:

Michel Cadot wrote on Sun, 01 March 2009 21:09
...
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), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a Test case: create table and insert statements along with the result you want with these data...


Hint for one solution: use WHERE NOT EXISTS, if you post a test case, I show you how to do it.

Regards
Michel

Previous Topic: What the Open cursor action?.(3 merged)
Next Topic: Trigger
Goto Forum:
  


Current Time: Thu Oct 23 10:07:33 CDT 2014

Total time taken to generate the page: 0.17365 seconds