CODEWITHSUNDEEP
mysqlsql
user826323
user826323

Reputation: 2338

mysql - return rows in ascending order

I have a table containing 392871961 rows like this.

     p_id        r_p_id      weight
     1           2           0.223923923
     1           3           0.12923923
     1           4           0.423926924
     1           5           0.143921921
     1           6           0.323923922
     1           7           0.223923728
     .
     .
     .
     2           1           0.123923921
     2           3           0.023923922
     2           4           0.223923926
     2           5           0.323923928
     2           6           0.223923921
     2           7           0.423923921
     .
     .
     .
     3           1           0.023923925
     3           2           0.223923922
     3           4           0.123923926
     3           5           0.223923929
     3           6           0.123923921
     3           7           0.523923922
     .
     .
     .

p_id goes up to a little over 6000 digit. weight represents some kind of priority between p_id and r_p_id.

I have two queries like this. A user is going to provide list of p_ids. It would be less than 10 p_ids.

first query = select r_p_id, weight from table where p_id in (....) order by weight

second query = select r_p_id, (weight*0.8) as m_weight from table where p_id in (....) order by (weight * 0.8)

I would like to combine these queries to return the r_p_id in ascending order by the sum of (weight from the first query) + (weight from the second query).

Can anybody tell me how to do it?

I'd appreciated it.

Upvotes: 1

Views: 83

Answers (3)

Marshall Tigerus
Marshall Tigerus

Reputation: 3764

select r_p_id, weight, (weight*0,8) as m_weight, (weight*1.8) as total_weight 
from table where p_id in (...) order by total_weight asc

weight + weight * .8 = weight * 1.8, so there's a quick sum of that column. Which of the weight columns you order by doesn't matter because they are all the same order

Upvotes: 0

Ravi Dhoriya ツ
Ravi Dhoriya ツ

Reputation: 4414

Try this

select t1.r_p_id, (t1.weight+t2.m_weight) as total_weight
from 
    (select r_p_id, weight from table where p_id in (....) ) as t1,
    (select r_p_id, (weight*0.8) as m_weight from table where p_id in (....)) as t2
where t1.r_p_id=t2.r_p_id
order by (t1.weight+t2.m_weight)

OR using JOIN keyword

select t1.r_p_id, (t1.weight+t2.m_weight) as total_weight
from 
    (select r_p_id, weight from table where p_id in (....) ) as t1 INNER JOIN
    (select r_p_id, (weight*0.8) as m_weight from table where p_id in (....)) as t2 ON t1.r_p_id=t2.r_p_id
order by (t1.weight+t2.m_weight)

You'l need to use JOIN between results of two queries.

EDIT:

Since MySQL supports order by [alias-name] as dg99 suggested, you can use directly order by total_weight

select t1.r_p_id, (t1.weight+t2.m_weight) as total_weight
from 
    (select r_p_id, weight from table where p_id in (....) ) as t1,
    (select r_p_id, (weight*0.8) as m_weight from table where p_id in (....)) as t2
where t1.r_p_id=t2.r_p_id
order by total_weight

Upvotes: 1

Dave Jemison
Dave Jemison

Reputation: 704

Um, maybe I'm missing something, but wouldn't it just be the following?

select r_p_id, weight, weight*0.8 AS m_weight from table where p_id in (....) order by weight

Mathmatically, you'll get the same order whether you do "order by weight" or "order by weight + 0.8*weight"

Upvotes: 0

Related Questions