CODEWITHSUNDEEP
phpmysqlsql
Igor Panda
Igor Panda

Reputation: 23

How to order SQL rows by value?

How to select user_ids by B meta_key and order by ratings meta_value?

    user_id    |    meta_key    |    meta_value
-----------------------------------------------------
       1                A                1
       1                B                1
       1                C                1
       1              rating             9
       8                A                1
       8                C                1
       8              rating           99999
       7                A                1
       7                B                1
       7                C                1
       7              rating            999

I need to get an IDs of users who have B meta_key and, then, to order IDs by user rating values.

Upvotes: 2

Views: 88

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269533

To do this, you need a self join:

select t.*
from t left outer join
     (select t.user_id, max(meta_value) as rating
      from t
      where t.meta_key = 'rating'
      group by t.user_id
     ) r
     on t.user_id = r.user_id
where meta_key = 'B'
order by r.rating

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 838036

Try this:

SELECT T1.user_id
    FROM yourtable T1 
    JOIN yourtable T2
        ON T1.user_id = T2.user_id
        AND T2.meta_key = 'rating'
    WHERE T1.meta_key = 'B'
    ORDER BY CAST(T2.meta_value AS SIGNED)

Upvotes: 1

Related Questions