mysql query to get charges

Question

I have the table below

relID    value   charge
  1        2       5
  1        8       2
  2        1       10
  2        4       6
  2        9       2

For the above table i need for a given value ex 10 to find what to charge for each relID

In the above for value<10 i need to get charge=5 for relID=1 and charge=2 for relID=2

I am trying to use 1 sql command to get it and i am kind of lost

Can anyony help

Thanks

Answer 1

Could you try to rephrase your question? What are you trying to do exactly?

Answer 2

Your question isn't very clear but I think this will work for you

SELECT t.relID,
(
    SELECT charge
    FROM table
    WHERE relID = t.relID
    AND value < 10
    ORDER BY value
    LIMIT 1
) AS charge
FROM table AS t

Answer 3

Let me rephrase.

Here is the table

relID    value   charge
  1        2       5
  1        8       2
  2        1       10
  2        4       6
  2        9       2

Explain the table :

Lets say that the value and charge are money. If the user has value 2 then i must charge with 5 using relID 1 If the user has value 8 then i must charge with 2 using relID 1 same for the relID 2

So when a user come with a value 10 i must find what to charge.So for the given value 10 i must find in the table all records with value<10.

In the example the values for value <10 are

For relID=1 are (2,8)
For relID=2 are (1,4,9)

Now for each relID i need to get the max value.

For relID=1 max value is 8 so charge is 2
For relID=2 max value is 9 so charge is 2

I plain english there are value rate

0-2 charge 5
2-8 charge 2

and ...

i hope to be clear now

Answer 4

select *
from Table t
where value =
  (select max(value)
   from Table
   where value <= 10
   and relId = t.relId)