MySQL sort by calculated value of 2 rows

Question

I'm trying to create a MySQL statement that will sort by a value calculated within the statement itself. My tables look like this:

posts
+----+-----------+--------------+
| ID | post_type | post_content |
+----+-----------+--------------+
|  1 |      post |        Hello |
|  2 |      post |        world |
+----+-----------+--------------+

postmeta
+---------+----------+------------+
| post_id | meta_key | meta_value |
+---------+----------+------------+
|       1 |    price |         50 |
|       1 |   retail |        100 |
|       2 |    price |         60 |
|       2 |   retail |         90 |
+---------+----------+------------+

I'm trying to calculate a value called savings (.5 for ID=1, .3 for ID=2) then sort by it. This is what I have so far but I'm not sure how to do a calculation across 2 rows (everything I found is about calculating between columns).

SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
WHERE wposts.ID = wpostmeta.post_id
AND wpostmeta.meta_key = 'Price'
AND wposts.post_type = 'post'
ORDER BY wpostmeta.meta_value DESC

Thanks for your help!

I know this isn't helpful, but if you're in charge of the DB, you should probably restructure it so that 'price' and 'retail are 2 separate columns... especially if those are the only 2 meta-keys. I think those meta-key type things should only be used for more obscure "settings" and such that you won't need to run complicated queries on. — mpen, Dec 01 '09 at 06:58
Well, this is the wordpress DB schema, supposed to be generic enough to handle most key value pairs for almost any post related items. — o.k.w, Dec 01 '09 at 08:15
Of course it's generic enough: it reimplements columns. Using real columns would still be a better choice, if it's possible. — , Dec 01 '09 at 18:33
Yes, WP can be a pain with the way it does things. I think prior to 2.5 it was putting the meta values in columns and moved each key=value pair to its own row after that. I'm sure there was a good reason for it, but the result is that the meta table has to be joined with itself to work on it. Thanks for your post! — Rod Boev, Dec 02 '09 at 00:59

score 2 · Answer 1 · edited Dec 01 '09 at 06:55

2

AFAIK You cannot calculate things "between rows" other than by using aggregate functions, which will not help you in this case.

Perhaps you can join your tables instead so you get one row:

SELECT wposts.*, pri.meta_value / ret.meta_value
FROM $wpdb->posts wposts
INNER JOIN $wpdb->postmeta pri
      ON  pri.post_id = wposts.ID
      AND pri.meta_key = 'price'
INNER JOIN $wpdb->postmeta ret
      ON  ret.post_id = wposts.ID
      AND ret.meta_key = 'retail'
WHERE wposts.post_type = 'post'
ORDER BY pri.meta_value / ret.meta_value

Tip: Never put more than one table in the FROM clause.

edited Dec 01 '09 at 06:55

o.k.w

25,490
6
66
63

answered Dec 01 '09 at 06:45

Brimstedt

3,020
22
32

Good one, fixed the typo of `pri/ret.ID` to `post_id`. +1 – o.k.w Dec 01 '09 at 06:56
2

"Never put more than one table in the FROM clause" - The QUESTION shows what's known as an old ANSI join. The syntax ("," instead of "INNER JOIN" is not incorrect per se, but best practices is to avoid it for reasons expressed well here: http://stackoverflow.com/questions/128965/is-there-something-wrong-with-joins-that-dont-use-the-join-keyword-in-sql-or-mys – micahwittman Dec 01 '09 at 07:04

score 2 · Accepted Answer · answered Dec 01 '09 at 06:58

Here's a simple join that'll calculate the savings per post which assumes that the meta_value is a numeric data type.

select posts.id,
       (retail.meta_value - price.meta_value) * 1.0 / retail.meta_value as savings
  from posts,
       (select * from postmeta where meta_key = 'price') as price,
       (select * from postmeta where meta_key = 'retail') as retail
 where posts.id = price.post_id
   and posts.id = retail.post_id
   and posts.post_type = 'post'
 order by savings;


+----+---------+
| id | savings |
+----+---------+
|  1 | 0.50000 |
|  2 | 0.33333 |
+----+---------+

I showed this thread to a co-worker who has more SQL experience than me and he said your solution is the best. I tried this and it worked well, so thank you! I just changed "select posts.id" to "select posts.*" so I can use all the other data. By the way, meta_value does not need to be numeric because the subtraction typecasts it (I think) correctly. Thanks again! — Rod Boev, Dec 02 '09 at 00:57

score 0 · Answer 3 · 2009-12-01T18:40:35.887

Edit: I misinterpreted your sample output of .5 as meaning 50 cents, likewise for .3 = 30 = 90 - 60, instead of the percentage you'd get from (100 - 50) / 100 and (90 - 60) / 90. Maybe you will still find this helpful, but it doesn't answer the question asked.

SELECT wposts.ID,
  SUM(wpostmeta.meta_value * (CASE
    WHEN wpostmeta.meta_key = 'price' THEN -1
    ELSE 1)
  ) AS savings
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
WHERE wposts.ID = wpostmeta.post_id
AND wposts.post_type = 'post'
GROUP BY wposts.ID;

The key is summing the meta_values but flipping the sign of price so you actually get retail minus price, grouped on each ID so the aggregate function SUM deals with each group independently. Whether it's smart to do all this logic here is a different question, however. :)

(You may have to tweak this syntax for MySQL.)

MySQL sort by calculated value of 2 rows

3 Answers3