Order MySQL data by row value

Question

I have two tables that looks like this:

Table: items

id | itemId
---|------
0  | 1
1  | 2
2  | 3

Table: item_specs

id | itemId | key          | values
---|--------|---------------
0  | 1      | itemreceived | 2012-06-01
1  | 1      | modelyear    | 1992
2  | 1      | model        | 2
3  | 2      | itemreceived | 2012-06-05
4  | 2      | modelyear    | 2003
5  | 2      | model        | 1
6  | 3      | itemreceived | 2012-07-05
7  | 3      | modelyear    | 2000
8  | 3      | model        | 3

My current query looks like this:

SELECT items.*, item_specs.* FROM item_specs
INNER JOIN item_specs ON items.itemId = item_specs.itemId
WHERE itemId IN(1,2,3)

How can I order the result by a key value, for example: model?

The result I'm looking for is something like this: (if I order by model)

id | itemId | key          | values
---|--------|---------------
3  | 2      | itemreceived | 2012-06-05
4  | 2      | modelyear    | 2003
5  | 2      | model        | 1
0  | 1      | itemreceived | 2012-06-01
1  | 1      | modelyear    | 1992
2  | 1      | model        | 2
6  | 3      | itemreceived | 2012-07-05
7  | 3      | modelyear    | 2000
8  | 3      | model        | 3

The content that is returned is ordered by the value that is that has the key model

This should be easy, but you need to clarify exactly what you want. Show the result you are looking for given the table you have above. Do you want to filter the table? Sort the table? Filter and sort the table? — Matt Burland, Jul 12 '12 at 14:59
@Patrik We encourage individual questions to have separate question entries. Yuor second question isn't really a StackOverflow type question, However, see the answer here: http://stackoverflow.com/a/11035969/1132642 (It's called an Entity Attribute Value model) — David Manheim, Jul 12 '12 at 15:15
@DavidManheim Ah thank you. Yes I am aware of that. Thought I just sneak that in there. — Patrik, Jul 12 '12 at 15:21
The rows in the result do not match with rows in the table. Where did that `id=6` come from, for example? — ypercubeᵀᴹ, Jul 12 '12 at 15:31
I'm currently updating the question. I will update in a few minutes — Patrik, Jul 12 '12 at 16:04

Nikola K. · Answer 1 · 2012-07-12T15:27:50.037

1

SELECT * FROM `table` WHERE `key` = 'model' ORDER BY `values` ASC

You have to manually specify a table type/storage engine. That can't be seen in the structure you provided.
Read more here.

edited Jul 12 '12 at 15:27

answered Jul 12 '12 at 14:53

Nikola K.

7,093
13
31
39

I have updated the question a bit. I want to sort by the value that belongs to the key model – Patrik Jul 12 '12 at 15:22

score 1 · Accepted Answer · answered Jul 13 '12 at 08:07

You need the model number for every row. You can do that with a join:

SELECT items.*, item_specs.* 
FROM item_specs
INNER JOIN item_specs ON items.itemId = item_specs.itemId
INNER JOIN item_specs aux ON (aux.key = 'model' and aux.itemID = item_specs.itemId)
WHERE item_specs.itemId IN(1,2,3)
ORDER BY aux.values/*this is the model*/, item_specs.id;

or with a subselect:

SELECT items.*, 
       item_specs.*, 
       (select aux.values 
        from item_specs aux 
        where aux.key = 'model' and aux.itemID = item_specs.itemId
        ) as model
FROM item_specs
INNER JOIN item_specs ON items.itemId = item_specs.itemId
WHERE item_specs.itemId IN(1,2,3)
ORDER BY model, item_specs.id;

score 0 · Answer 3 · answered Jul 12 '12 at 15:08

0

It seems you want to use an order by clause. This will order by the columns you need. You can also do sneaky things here, like insert a true/false value for what you order by first.

SELECT * FROM `table` 
   Order by (case When Key='model' then 0 else 1 end), values

See, for instance, http://blog.sqlauthority.com/2007/07/17/sql-server-case-statement-in-order-by-clause-order-by-using-variable/

answered Jul 12 '12 at 15:08

David Manheim

2,553
2
27
42

I think you understand me right but I have updated the question. I will take a closer look at your answer. – Patrik Jul 12 '12 at 15:23

score 0 · Answer 4 · answered Jul 12 '12 at 15:27

0

SELECT * FROM `table` 
WHERE `key` = 'model' 
ORDER BY `values`;

answered Jul 12 '12 at 15:27

GhostInTheSecureShell

990
8
14

Order MySQL data by row value

4 Answers4