OraFAQ Forum: SQL & PL/SQL » Query for Matching Record --> as much matches

Home » SQL & PL/SQL » SQL & PL/SQL » Query for Matching Record --> as much matches

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query for Matching Record --> as much matches [message #671983]

Thu, 27 September 2018 03:20

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

John Watson
Messages: 8836
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

cookiemonster
Messages: 13904
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?

Report message to a moderator

Re: Query for Matching Record --> as much matches [message #671986 is a reply to message #671985]

Thu, 27 September 2018 05:00

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

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

Report message to a moderator

Re: Query for Matching Record --> as much matches [message #671988 is a reply to message #671983]

Thu, 27 September 2018 05:32

Michel Cadot
Messages: 68447
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.

Report message to a moderator

Re: Query for Matching Record --> as much matches [message #671990 is a reply to message #671988]

Thu, 27 September 2018 05:45

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

Report message to a moderator

Re: Query for Matching Record --> as much matches [message #671997 is a reply to message #671990]

Thu, 27 September 2018 07:20

Michel Cadot
Messages: 68447
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.

Report message to a moderator

Re: Query for Matching Record --> as much matches [message #671998 is a reply to message #671983]

Thu, 27 September 2018 07:35

Solomon Yakobson
Messages: 3227
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.

Report message to a moderator

Re: Query for Matching Record --> as much matches [message #671999 is a reply to message #671997]

Thu, 27 September 2018 07:36

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';

Report message to a moderator

Re: Query for Matching Record --> as much matches [message #672004 is a reply to message #671998]

Thu, 27 September 2018 09:23

cookiemonster
Messages: 13904
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.

Report message to a moderator

Re: Query for Matching Record --> as much matches [message #672005 is a reply to message #671986]

Thu, 27 September 2018 09:26

cookiemonster
Messages: 13904
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.

Report message to a moderator

Re: Query for Matching Record --> as much matches [message #672022 is a reply to message #672005]

Fri, 28 September 2018 04:59

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;

Report message to a moderator

Previous Topic:	Using LISTAGG in a subquery
Next Topic:	Listing all dates in a date range

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 28 17:07:10 CDT 2023