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,