Home » SQL & PL/SQL » SQL & PL/SQL » Query for Matching Record --> as much matches
Query for Matching Record --> as much matches [message #671983] Thu, 27 September 2018 03:20 Go to next message
myclassic
Messages: 136
Registered: December 2006
Location: Pakistan
Senior Member
I want a Query that should fetch a single record based on match conditions:
table looks like
create table SERVICES
(
  srvc     VARCHAR2(10) not null,
  location VARCHAR2(10),
  grp      VARCHAR2(10),
  empno    VARCHAR2(10),
  price    NUMBER default 0 not null);
insert into services(srvc, location, empno, grp, price)
values('srv1', null, null, null, 1500);
insert into services(srvc, location, empno, grp, price)
values('srv1', null, '123', null, 2000);
insert into services(srvc, location, empno, grp, price)
values('srv1', null, '123', 'A', 2500);
commit;

I want to query this table based on following conditions:
srvc = 'srv1' and location='home' and empno = '123' and grp = 'A' ----> It should return price 2500

srvc = 'srv1' and location='home' and empno = '123' and grp = 'B' ----> It should return price 2000

srvc = 'srv1' and location='office' and empno = '456' and grp = 'B' ----> It should return price 1500

Please help me in building the query.
regards

[Updated on: Thu, 27 September 2018 03:22]

Report message to a moderator

Re: Query for Matching Record --> as much matches [message #671984 is a reply to message #671983] Thu, 27 September 2018 03:29 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
??? No query will do what you want, because none of the rows satisfy the predicate location='home' or location='office' or several other filters.

[Updated on: Thu, 27 September 2018 03:30]

Report message to a moderator

Re: Query for Matching Record --> as much matches [message #671985 is a reply to message #671984] Thu, 27 September 2018 04:55 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
So you want to match rows where the columns match the specified value or the column is null
and then return the price from the row which has the most actual matches.
What happens if there are two rows with the same number of matches?
Say you specify srvc = 'srv1' and location='home' and empno = '123'
and there's one row with srv1 and home
and one row with srv1 and 123
Which do you want in that case?
Re: Query for Matching Record --> as much matches [message #671986 is a reply to message #671985] Thu, 27 September 2018 05:00 Go to previous messageGo to next message
myclassic
Messages: 136
Registered: December 2006
Location: Pakistan
Senior Member
Sir All rows are unique. so first question is ruled out.
alter table SERVICES
  add constraint uq_services unique (SRVC, LOCATION, GRP, EMPNO);

I want to match the closest one Row

[Updated on: Thu, 27 September 2018 05:01]

Report message to a moderator

Re: Query for Matching Record --> as much matches [message #671987 is a reply to message #671985] Thu, 27 September 2018 05:12 Go to previous messageGo to next message
myclassic
Messages: 136
Registered: December 2006
Location: Pakistan
Senior Member
cookiemonster wrote on Thu, 27 September 2018 04:55
So you want to match rows where the columns match the specified value or the column is null
and then return the price from the row which has the most actual matches.
What happens if there are two rows with the same number of matches?
Say you specify srvc = 'srv1' and location='home' and empno = '123'
and there's one row with srv1 and home
and one row with srv1 and 123
Which do you want in that case?
Yes I want the maximum match, my WHERE clause will be having ALL values
So it should return EXACT match or NULL
Re: Query for Matching Record --> as much matches [message #671988 is a reply to message #671983] Thu, 27 September 2018 05:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I answer exactly the same question in AskTom several years ago.
Try to search and find it.

Re: Query for Matching Record --> as much matches [message #671990 is a reply to message #671988] Thu, 27 September 2018 05:45 Go to previous messageGo to next message
myclassic
Messages: 136
Registered: December 2006
Location: Pakistan
Senior Member
Michel Cadot wrote on Thu, 27 September 2018 05:32

I answer exactly the same question in AskTom several years ago.
Try to search and find it.

Sir you are the only hope.... Confused
Re: Query for Matching Record --> as much matches [message #671997 is a reply to message #671990] Thu, 27 September 2018 07:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I won't spend time to search in the site to help you if you don't do it yourself to help yourself.

Re: Query for Matching Record --> as much matches [message #671998 is a reply to message #671983] Thu, 27 September 2018 07:35 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
And why srvc = 'srv1' and location='home' and empno = '123' and grp = 'B' ----> It should return price 2000?

All services have srvc = 'srv1', so all 3 match so far and have same match score. None of them have location = 'home', so again all 3 services have same score. Only 2 services have empno = '123', so price = 1500 is discarded. None of remaining two services have grp = 'B', so both remaining 2 services end up having highest score. You need to define rules for ties. Anyway:

SQL> variable srvc varchar2(10)
SQL> variable location varchar2(10)
SQL> variable empno varchar2(10)
SQL> variable grp varchar2(10)
SQL> exec :srvc := 'srv1'; :location :='home'; :empno := '123'; :grp := 'A';

PL/SQL procedure successfully completed.

SQL> with t1 as (
  2              select  s.*,
  3                      case srvc
  4                        when :srvc then 1
  5                        else 0
  6                      end + case location
  7                              when :location then 1
  8                              else 0
  9                            end + case empno
 10                                    when :empno then 1
 11                                    else 0
 12                                  end + case grp
 13                                          when :grp then 1
 14                                          else 0
 15                                        end score
 16                from  services s
 17             ),
 18       t2 as (
 19              select  t1.*,
 20                      dense_rank() over(order by score desc) rnk
 21                from  t1
 22             )
 23  select  price
 24    from  t2
 25    where rnk = 1
 26  /

     PRICE
----------
      2500

SQL> exec :grp := 'B';

PL/SQL procedure successfully completed.

SQL> with t1 as (
  2              select  s.*,
  3                      case srvc
  4                        when :srvc then 1
  5                        else 0
  6                      end + case location
  7                              when :location then 1
  8                              else 0
  9                            end + case empno
 10                                    when :empno then 1
 11                                    else 0
 12                                  end + case grp
 13                                          when :grp then 1
 14                                          else 0
 15                                        end score
 16                from  services s
 17             ),
 18       t2 as (
 19              select  t1.*,
 20                      dense_rank() over(order by score desc) rnk
 21                from  t1
 22             )
 23  select  price
 24    from  t2
 25    where rnk = 1
 26  /

     PRICE
----------
      2000
      2500

SQL> exec :empno := '456';

PL/SQL procedure successfully completed.

SQL> with t1 as (
  2              select  s.*,
  3                      case srvc
  4                        when :srvc then 1
  5                        else 0
  6                      end + case location
  7                              when :location then 1
  8                              else 0
  9                            end + case empno
 10                                    when :empno then 1
 11                                    else 0
 12                                  end + case grp
 13                                          when :grp then 1
 14                                          else 0
 15                                        end score
 16                from  services s
 17             ),
 18       t2 as (
 19              select  t1.*,
 20                      dense_rank() over(order by score desc) rnk
 21                from  t1
 22             )
 23  select  price
 24    from  t2
 25    where rnk = 1
 26  /

     PRICE
----------
      1500
      2000
      2500

SQL> 

SY.
Re: Query for Matching Record --> as much matches [message #671999 is a reply to message #671997] Thu, 27 September 2018 07:36 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
try

select price 
from services
where srvc = 'srv1'
and (location is null or location = 'home')
and empno = '123'
and grp = 'A';
Re: Query for Matching Record --> as much matches [message #672004 is a reply to message #671998] Thu, 27 September 2018 09:23 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Solomon Yakobson wrote on Thu, 27 September 2018 13:35
And why srvc = 'srv1' and location='home' and empno = '123' and grp = 'B' ----> It should return price 2000?

All services have srvc = 'srv1', so all 3 match so far and have same match score. None of them have location = 'home', so again all 3 services have same score. Only 2 services have empno = '123', so price = 1500 is discarded. None of remaining two services have grp = 'B', so both remaining 2 services end up having highest score.
It appears to be column matches supplied value or column is null.
Column has different value is ignored.

So specifying grp B excludes the row where grp is A. That leaves one row that matches on empno - the one with 2000.
Re: Query for Matching Record --> as much matches [message #672005 is a reply to message #671986] Thu, 27 September 2018 09:26 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
myclassic wrote on Thu, 27 September 2018 11:00
Sir All rows are unique. so first question is ruled out.
alter table SERVICES
  add constraint uq_services unique (SRVC, LOCATION, GRP, EMPNO);

I want to match the closest one Row
Uniqueness doesn't rule out my question at all.
Say you have:
insert into services(srvc, location, empno, grp, price)
values('srv1', null, '123', null, 2000);
insert into services(srvc, location, empno, grp, price)
values('srv1', null, NULL, 'A', 2500);
And you're querying srv1, home, 123, A.
Which row should be returned.
As already mentioned you need to define rules for ties.
Re: Query for Matching Record --> as much matches [message #672022 is a reply to message #672005] Fri, 28 September 2018 04:59 Go to previous message
myclassic
Messages: 136
Registered: December 2006
Location: Pakistan
Senior Member
Sir Solomon Yakobson and Sir Cookiemonster are Rgiht.
It means we need to define priority of columns in order by clause:

https://asktom.oracle.com/pls/apex/f?p=100:11:::NO::P11_QUESTION_ID:9538922100346943929

select * from (
  select * from services
  where  ( srvc = 'srv1' or srvc is null )
  and    ( location = 'home' or location is null )
  and    ( empno = '123' or empno is null )
  and    ( grp = 'A' or grp is null )
  order  by srvc, location, grp, empno
)
where  rownum = 1;
Previous Topic: Using LISTAGG in a subquery
Next Topic: Listing all dates in a date range
Goto Forum:
  


Current Time: Thu Mar 28 17:51:32 CDT 2024