CODEWITHSUNDEEP
mysqlsql
c10b10
c10b10

Reputation: 357

SQL sorting with multiple criteria

I have two tables.

Tab1:

+------------+
| id | title |
+------------+
| 1 | B      |
| 2 | C      |
| 3 | A      |
| 4 | A      |
| 5 | A      |
| 6 | A      |
| ...        |
+------------+

Tab2:

+-------------------------------------------+
| id | item_id | item_key |     item_value  |
+-------------------------------------------+
| 1  | 1       | value    | $4              |
| 2  | 1       | url      | http://h.com/   |
| 3  | 2       | value    | $5              |
| 4  | 3       | url      | http://i.com/   |
| 5  | 3       | value    | $1              |
| 6  | 3       | url      | http://y.com/   |
| 7  | 4       | value    | $2              |
| 8  | 4       | url      | http://z.com/   |
| 9  | 5       | value    | $1              |
| 10 | 5       | url      | http://123.com/ |
| ...                                       |
+-------------------------------------------+

How do I make it so I get a table of ids from Tab1 in order according to criteria from both tables. The criteria are the following:

The resulting table with the ordered results would be:

+------------+
| id | title |
+------------+
| 4 | A      |
| 5 | A      |
| 3 | A      |
| 6 | A      |
| 1 | B      |
| 2 | C      |
| ...        |
+------------+

The results should include items that don't have the one, both, or none of the fields from Tab2 set.

Upvotes: 2

Views: 2990

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 180887

As far as I understand, a simple join will do it. You'll have to join Tab2 twice, since you want to order by values from both rows.

SELECT Tab1.id, Tab1.title
FROM Tab1
JOIN Tab2 t2_val ON t2_val.item_id = Tab1.id AND t2_val.item_key='value'
JOIN Tab2 t2_url ON t2_url.item_id = Tab1.id AND t2_url.item_key='url'
ORDER BY title, 
         t2_val.item_value DESC,
         t2_url.item_value LIKE '%123.com%' DESC

An SQLfiddle to test with.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269503

A little complicated, because when you do the join you will get multiple rows. Here is an approach that aggregates tab2 before doing the join:

select t1.*
from Tab1 t1 left outer join
     (select id,
             max(case when item_key = 'value' then item_value end) as value,
             max(case when item_key = 'url' then item_value end) as url
      from Tab2 t2
      group by id
     ) t2
     on t1.id = t2.id
order by t1.title, t2.value desc,
         (t2.url like '%123.com%') desc;

Upvotes: 2

Related Questions