From: | "Raouf" <aimeur(at)prodigy(dot)net> |
---|---|
To: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | best match question: please help |
Date: | 2002-07-04 07:18:02 |
Message-ID: | 002c01c2232a$f025c180$0201a8c0@pavilion |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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(at)prodigy(dot)net (my e-mail) because I've just subscribe to psql-novice mailing list I don't have the confirmation yet.
thanks,
From | Date | Subject | |
---|---|---|---|
Next Message | Manfred Koizar | 2002-07-04 13:18:12 | Re: best match question: please help |
Previous Message | Josh Jore | 2002-07-03 17:53:18 | Re: sequences what does ::text mean ? |