How to write a query selecting reasonable trade-offs?

In a table I have two columns obs and abd. I am interested in finding low values for both obs and abd, but a low value for abd is more important than a low value for obs. In the real world I have a trade-off between low obs and low abd which is not easy to define mathematically and would be difficult to explain, but the point is that what I want to see from the query is some data that would make reasonable trade-offs. I would like to know several data pairs within a range of obs values. For example:

mysql> select obs, abd from flow where obs < 2000 order by abd,obs limit 10;

    +------+--------------+
    | obs  | abd          |
    +------+--------------+
    | 1372 | 0.0000004744 |
    | 1734 | 0.0000017704 |
    | 1010 | 0.0000017716 |
    | 1999 | 0.0000017716 |
    | 1637 | 0.0000036486 |
    |  383 | 0.0000066084 |
    |  745 | 0.0000066084 |
    | 1107 | 0.0000066084 |
    | 1469 | 0.0000066084 |
    | 1831 | 0.0000066084 |
    +------+--------------+

From the above results, it can be seen that there are several values of obs that have the same abd value. I am interested in only the one with the lowest obs value for every abd value. All the other duplicate abd values should be discarded. This is easily done by using a group by clause:

mysql> select obs, abd from flow where obs < 2000 group by abd order by abd,obs limit 10;

    +------+--------------+
    | obs  | abd          |
    +------+--------------+
    | 1372 | 0.0000004744 |
    | 1734 | 0.0000017704 |
    | 1010 | 0.0000017716 |
    | 1637 | 0.0000036486 |
    |  383 | 0.0000066084 |
    |  648 | 0.0000066096 |
    | 1540 | 0.0000097586 |
    | 1928 | 0.0000109544 |
    | 1566 | 0.0000119724 |
    |  913 | 0.0000119736 |
    +------+--------------+

So far, so good. Now the problem is that aft开发者_如何学运维er having seen the first entry where obs is 1372 and abd is 0.0000004744 I am not interested in seeing the second entry where both obs and abd are higher. I am interested in seeing the third entry where obs is lower but abd is higher because there is a trade-off between obs and abd. Again, I am not interested in seeing the fourth entry because it has values of obs and abd that are both higher than what is already shown in the third entry. The fifth entry is one I am particularly interested in because although the abd value is somewhat higher, the obs value is far lower. As for the rest of the entries, I would like to not see them because they have both higher obs and abd than what has already been seen.

In summary, I would like a query that would show me:

+------+--------------+
| obs  | abd          |
+------+--------------+
| 1372 | 0.0000004744 |
| 1010 | 0.0000017716 |
|  383 | 0.0000066084 |
+------+--------------+

plus another seven entries where obs continues to decrease and abd continues to increase. Is there a way to get the set of data pairs with one query without resorting to a procedure?

To get the lowest obs by abd, here is how you go:

select min(obs), abd
from flow
where obs < 2000 
group by abd 
order by abd

But it seems to me like the trade-off rule is kind of ad-hoc. You should try to figured out the best rules you can think of and them lay them on the table so that we can achieve what you are trying to do.

You could just say you want a Pareto front

It's not going to be fast but try this:

SELECT a.obs
     , a.abd
FROM flow a
  LEFT JOIN flow b
    ON   ( b.obs <= a.obs AND b.abd <  a.abd )
      OR ( b.obs <  a.obs AND b.abd <= a.abd )
WHERE b.obs IS NULL
ORDER BY a.abd

Also:

SELECT a.obs
     , a.abd
FROM flow a
WHERE NOT EXISTS
  ( SELECT 1
    FROM flow b
    WHERE ( b.obs <= a.obs AND b.abd <  a.abd )
      OR ( b.obs <  a.obs AND b.abd <= a.abd )
  )
ORDER BY a.abd

And this:

SELECT a.obs
     , a.abd
FROM flow a
WHERE NOT EXISTS
  ( SELECT 1
    FROM flow b
    WHERE b.obs <= a.obs
      AND b.abd <  a.abd
  )
  AND NOT EXISTS
  ( SELECT 1
    FROM flow b
    WHERE b.obs < a.obs
      AND b.abd = a.abd
  )
ORDER BY a.abd

or this:

SELECT a.obs
     , a.abd
FROM flow a
WHERE NOT EXISTS
  ( SELECT 1
    FROM flow b
    WHERE b.obs <= a.obs
      AND b.abd <= a.abd
      AND (b.obs, b.abd) <> (a.obs, a.abd)
  )
ORDER BY a.abd

Check which of the 4 is faster. I would guess the 4th, if you have indexes on obs and abd. Or better, (as Unreason pointed), two indexes: one on (obs, abd) and one on abd.

UPDATE: (small correction on 3rd query).