You Asked
I have a table for Vehicle owners as follows. The table has 3 columns, Customer id, Vehicle Identification number, and whether the customer is a Primary or Secondary driver on the vehicle.
Customer_ID, Vehicle_VIN, Relationship
0001, 12345678, PRI
0001, 23456789, SEC
0001, 12345678, SEC
0002, 23456789, PRI
0002, 12345678, SEC
A customer can either be Primary (PRI) or Secondary (SEC) on a vehicle but not both. However the table has some erroneous data such that some customers are listed as Primary and secondary on the same vehicle.
I need to write a query such that If a customer is listed as primary and secondary on a vehicle, the query should only return only the Primary row.
For example, when executed on the above data the query should return the following:
Customer_ID, Vehicle_VIN, Relationship
0001, 12345678, PRI
0001, 23456789, SEC
0002, 23456789, PRI
0002, 12345678, SEC
Thanks,
Dave
and Connor said...
I'm taking advantage of the fact that "PRI" sorts before "SEC", but you might want to code that sort order explicitly. Anyway...
SQL> create table c ( cust varchar2(10), vin int, tag varchar2(10));
Table created.
SQL>
SQL> insert into c values ('0001', 12345678, 'PRI');
1 row created.
SQL> insert into c values ('0001', 23456789, 'SEC');
1 row created.
SQL> insert into c values ('0001', 12345678, 'SEC');
1 row created.
SQL> insert into c values ('0002', 23456789, 'PRI');
1 row created.
SQL> insert into c values ('0002', 12345678, 'SEC');
1 row created.
SQL>
SQL> select * from c;
CUST VIN TAG
---------- ---------- ----------
0001 12345678 PRI
0001 23456789 SEC
0001 12345678 SEC
0002 23456789 PRI
0002 12345678 SEC
5 rows selected.
SQL>
SQL> select c.* ,
2 row_number() over ( partition by cust,vin order by tag ) as seq
3 from c;
CUST VIN TAG SEQ
---------- ---------- ---------- ----------
0001 12345678 PRI 1
0001 12345678 SEC 2
0001 23456789 SEC 1
0002 12345678 SEC 1
0002 23456789 PRI 1
5 rows selected.
SQL>
SQL> select *
2 from
3 ( select c.* ,
4 row_number() over ( partition by cust,vin order by tag ) as seq
5 from c
6 )
7 where seq = 1;
CUST VIN TAG SEQ
---------- ---------- ---------- ----------
0001 12345678 PRI 1
0001 23456789 SEC 1
0002 12345678 SEC 1
0002 23456789 PRI 1
4 rows selected.
SQL>
SQL>
Hope this helps.
Rating
(5 ratings)
Is this answer out of date? If it is, please let us know via a Comment