mysql - SQL SUM top 5 values then JOIN results -

i want return top 5 voted items, sorted vote count. suppose best way sum each row's votes_up , votes_down, take top 5 of , join other table.

here existing tables, items.id maps votes.item_id:

items +----+--------+---------+ | id |  name  | site_id | +----+--------+---------+ | 10 | box    |     111 | | 11 | hammer |     222 | | 12 | drill  |     333 | | 13 | nail   |     444 | +----+--------+---------+  votes +----+---------+----------+------------+ | id | item_id | votes_up | votes_down | +----+---------+----------+------------+ |  1 |      10 |       25 |         20 | |  2 |      11 |      200 |        100 | |  3 |      12 |      100 |         50 | |  4 |      13 |       50 |         20 | +----+---------+----------+------------+ 

these results back:

+--------+-------+ |  name  | votes | +--------+-------+ | hammer |   100 | | drill  |    50 | | nail   |    30 | | box    |     5 | +--------+-------+ 

select   items.name, votes_up-votes_down votes_num     items, votes    items.id = votes.item_id order votes_num desc limit    5; 

databases first join limit, otherwise lose tuples. can verify using explain select ... statements if you're using mysql or postgresql. if want top 5 first, have manually:

select i.name, v.votes_num votes_num   items i,    (select item_id, votes_up-votes_down votes_num      votes order votes_num desc limit 5) v  i.id = v.item_id order votes_num desc; 

checkout demo; returns results want.

name    votes_num hammer  100 drill   50 nail    30 box     5