Hi all,

basically, what I am having trouble with is this:

I have a value stored in a table for each member, let's call it \$my_value. Each member has a \$my_value, which is an integer.

What I want to do is as follows...
Lets assume I log in as user A. I would like to list all the member's in the database where the other users' \$my_value is no more than 5% larger of user A's \$my_value and no more than 5% lower than user A's \$my_value.

Any assistance would be very much appreciated!

3
Contributors
4
Replies
5
Views
7 Years
Discussion Span
Last Post by flynismo

select all values in table for which this fieldvalue lies between givenvalue*0.95 and givenvalue*1.05 wont that work ?

My apologies, I just realized that I didn't explain it correctly.

I can get the correct range of \$my_value to populate the query, but what I need to do is display the result as a percentage.

For example, User A has a \$my_value of 100. User B has a \$my_value of 97.
So, if User A performs a search, he will find User B, since User B falls within the +/- 5% range.
But rather than displaying User B's \$my_value, I want to convert his \$my_value into a percentage when compared to User A.

In other words:

1. User A (\$my_value = 100) performs search and finds User B (\$my_value = 97).

2. User A sees User B as 97% (since 97 is 97% of User A's 100)

.... or ....

3. User C (\$my_value = 19) finds User D (\$my_value = 20)

4. User D's \$my_value of 20 is shown as 95% (since 19 is 95% of 20)

Hi try this one... :)

``````select * from table
where myvalue >= ((select myvalue from table where name='A') - (select myvalue from table where name='A') * 5/100 )
and myvalue <= ((select myvalue from table where name='A') + (select myvalue from table where name='A') * 5/100 )``````

It will also show the records including A's myvalue,if you don't want it to be listed then put one more where condition at the end. :D

I hope it helps...

It it helps,mark it as solved so it can help others...

That looks like it should work, I will give it a try!

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.