Jacob Talbot
Jacob Talbot

Reputation: 101

MySQL query sorting

I'm trying to order results of a MySQL query based on a users set data in a field under their row.

I have a table called "assigned", set up like this:

id  user_id  item_id
--  -------  -------
1   1        1
2   1        23
3   1        304

I want to be able to order these results based on the users table which will be stored like this:

3,1,2

Any ideas?

Upvotes: 2

Views: 74

Answers (3)

symcbean
symcbean

Reputation: 48387

You should never assume that rows in a table within a relational database have any implicit ordering. i.e. while today 'SELECT * FROM users' might return the data in the order you want, it may be different tomorrow.

The same applies in principle to columns although MySQL is unusual compared with other relational databases in that it does allow you to explicitly state a column order.

Add a column to the users table indicating the ordinal value and sort on that with a join to your assigned table.

Upvotes: 0

JMax
JMax

Reputation: 26611

You should use the keyword ORDER BY to get the results ordered the way you want :

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC

in your case, you would

ORDER BY user_id

If it doesn't answer your question, please tell us more precisely what you want.

Max

Upvotes: 1

Matteo Alessani
Matteo Alessani

Reputation: 10422

you can use the FIELD option:

SELECT * FROM assigned ORDER BY FIELD(id,3,1,2)

where id is the field of the table for which you want to order and the followings are the ids ordered.

Upvotes: 2

Related Questions