CODEWITHSUNDEEP
sqloracle-database
Hawk
Hawk

Reputation: 5170

Choose rows based on two connected column values in one statement - ORACLE

First, I'm not sure if the title represent the best of the issue. Any better suggestion is welcomed. My problem is I have the following table: 

+----+----------+-------+-----------------+
| ID | SUPPLIER | BUYER | VALIDATION_CODE |
+----+----------+-------+-----------------+
|  1 | A        | Z     |       937886521 |
|  2 | A        | X     |       937886521 |
|  3 | B        | Z     |       145410916 |
|  4 | C        | V     |      775709785  |
+----+----------+-------+-----------------+

I need to show SUPPLIERS A and B which have BUYER Z, X. However, I want this condition to be one-to-one relationship rather than one-to-many. That is, for the supplier A, I want to show the column with ID: 1, 2. For the supplier B, I want to show the column 3 only. The following script will show the supplier A with all possible buyers (which I do not want): 

SELECT *   
FROM   validation   
WHERE  supplier IN ( 'A', 'B' )   
       AND buyer IN ( 'X', 'Z');

This will show the following pairs: (A,Z), (A,X), (B, Z). I need to show only the following: (A,X)(B,Z) in one statement.

The desired result should be like this: 

+----+----------+-------+-----------------+
| ID | SUPPLIER | BUYER | VALIDATION_CODE |
+----+----------+-------+-----------------+
|  2 | A        | X     |       937886521 |
|  3 | B        | Z     |       145410916 |
+----+----------+-------+-----------------+

Upvotes: 0

Views: 115

Answers (4)

Hamidreza
Hamidreza

Reputation: 3128

try this query:

select ID,SUPPLIER,BUYER,VALIDATION_CODE from
(select
t2.*,t1.counter
from
validation t2,
(select supplier,count(supplier) as counter from hatest group by supplier)t1
where 
t1.supplier = t2.supplier)t3
where t3.supplier in('A','B') and
id = case when t3.counter > 1 then 
(select max(id) from validation t4 where t4.supplier = t3.supplier) else t3.id end;

Upvotes: 0

theodojo
theodojo

Reputation: 206

Alternatively, you could try this:

SELECT id
     , supplier
     , buyer
     , validation_code 
FROM (SELECT id
             ,max(id) OVER(PARTITION BY supplier) AS maxid
             ,supplier
             ,buyer
             ,validation_code
              FROM sample) AS x
WHERE x.id=x.maxid

You may have a look to the results of the inner SQL statement to see what it does.

Upvotes: 0

PM 77-1
PM 77-1

Reputation: 13344

See if this what you need:

SELECT MAX(id),
       supplier, 
       MAX(buyer), 
       MAX(validation_code)
FROM
(SELECT *
 FROM Validation
 WHERE supplier IN ( 'A', 'B' ) AND buyer IN ( 'X', 'Z')
) filtered
GROUP BY supplier;

SQL Fiddle

I used GROUP BY supplier to flatten the table and included maximum values of ID, Buyer, and Validation_Code.

Upvotes: 0

tawman
tawman

Reputation: 2498

You can update the WHERE clause to filter on the desired pairs:

select *
from sample
where (upper(supplier),upper(buyer))
in (('A','X'),('A','Y'),('A','Z'),('B','X'),('B','Y'),('B','Z'));

I used the UPPER function based on your mixed case examples.

Upvotes: 2

Related Questions