Postgres Pro
Downloads
Home   >   mailing lists

Thread: best match question: please help

best match question: please help

From
"Raouf"
Date:
Hi all,
 
It may be a silly question but I can not figure out how to solve my problem, here it is:
 
table snmpOids
---------------------------------------------------------------------------------
id       |          objectId                          |            objectDescr
---------------------------------------------------------------------------------
1        |          1.3.6                       |            name1
2        |          1.3.6.1                    |            name2    
3        |          1.3.6.1.2                 |            name3   
4        |          1.3.6.1.2.1              |            name4   
5        |          1.0.2.3.1                 |            name5
6        |          1.1.2.3.1                 |            name6
 
 
type is VARCHAR for all the columns of snmpOids.
 
Let's say that I have an objectId equal to 1.3.6.1.2.1.1.1 and I want to get back from my query the best match on the objectId, which is 1.3.6.1.2.1 (closest objectId to 1.3.6.1.2.1.1.1 ). How can I do that with the less querues as possible ?
I was thinking of using the strpos() function like this
 
select max(length(objectId)) from snmpOids where  strpos('1.3.6.1.2.1.1.1',objectid) > 1;
 
it gives me :
11 which correponds to the length of 1.3.6.1.2.1, the best match.
 
Now my problem is that I don't know how to get the objectId column. If I use
 
select objectId,max(length(objectId)) from snmpOids where  strpos('1.3.6.1.2.1.1.1',objectid) > 1 order by objectId;
it gives me : 
 
1.3.6                       |            5
1.3.6.1                     |            7    
1.3.6.1.2                  |            9   
1.3.6.1.2.1               |            11
 
 
I just want the best match, not all the matches. I want a result like this:
 1.3.6.1.2.1               |            11  
 
 
PLEASE HOW CAN I GET THIS OUTPUT ????
please reply also to aimeur@prodigy.net (my e-mail) because I've just subscribe to psql-novice mailing list I don't have the confirmation yet.
 
thanks,

Re: best match question: please help

From
Manfred Koizar
Date:
On Thu, 4 Jul 2002 00:18:02 -0700, "Raouf" <aimeur@prodigy.net> wrote:
>table snmpOids
>---------------------------------------------------------------------------------
>id       |          objectId                          |            objectDescr
>---------------------------------------------------------------------------------
>1        |          1.3.6                       |            name1
>2        |          1.3.6.1                    |            name2
>3        |          1.3.6.1.2                 |            name3
>4        |          1.3.6.1.2.1              |            name4
>5        |          1.0.2.3.1                 |            name5
>6        |          1.1.2.3.1                 |            name6
>
>type is VARCHAR for all the columns of snmpOids.
>
>Let's say that I have an objectId equal to 1.3.6.1.2.1.1.1 and
>I want to get back from my query the best match on the objectId,
>which is 1.3.6.1.2.1 (closest objectId to 1.3.6.1.2.1.1.1 ).

SELECT *
  FROM snmpOids
 WHERE '1.3.6.1.2.1.1.1' LIKE objectId || '%'
 ORDER BY objectId DESC
 LIMIT 1;

should do what you need, but don't tell anybody you got that from me
when it kills your performance ;-)

Servus
 Manfred