CODEWITHSUNDEEP
mysqlsql
Ted
Ted

Reputation: 4166

MySQL: return result with its order

Is there a way to return result with its order?

Sample: Run the following query on the quiz table:

select q.category_id, q.quiz_id, concat('Quiz ',q.name) name
from quiz q
where q.category_id = 11
order by q.category_id ASC
limit 2
offset 2;

Table quiz (structure):

+-------------+---------+-------+
| category_id | quiz_id | name  |
+-------------+---------+-------+
| 10          | 10      | math  |
| 11          | 10      | sport |  => Quiz Sport 1
| 11          | 11      | sport |  => Quiz Sport 2
| 12          | 10      | Geo.  |
| 11          | 15      | sport |  => Quiz Sport 3
| 11          | 12      | sport |  => Quiz Sport 4
| 10          | 17      | math  |
| 11          | 20      | sport |  => Quiz Sport 5
| 11          | 22      | sport |  => Quiz Sport 6
| 10          | 19      | math  |
+-------------+---------+-------+

returns:

+-------------+---------+------------+
| category_id | quiz_id | name       |
+-------------+---------+------------+
| 11          | 15      | Quiz sport |
| 11          | 12      | Quiz sport |
+-------------+---------+------------+

http://sqlfiddle.com/#!9/110752/2

Is there a way to return a result with the Quiz number order, like this:

+-------------+---------+--------------+
| category_id | quiz_id | name         |
+-------------+---------+--------------+
| 11          | 15      | Quiz sport 3 |
| 11          | 12      | Quiz sport 4 |
+-------------+---------+--------------+

Upvotes: 1

Views: 52

Answers (3)

sgeddes
sgeddes

Reputation: 62831

You need to establish a row number to do this. Doing so means you need a unique field to order by to ensure the order of the results. You can get the row number using user-defined variables in mysql. Here's an example reordering by quiz_id:

select * 
from (select q.category_id, q.quiz_id, @rn := @rn + 1, concat('Quiz ',q.name, @rn) name
    from quiz q, (select @rn := 0) t
    where q.category_id = 11
    order by q.category_id ASC, q.quiz_id) t
order by category_id ASC, quiz_id
limit 2
offset 2;

Upvotes: 2

goodguy5
goodguy5

Reputation: 419

I found a similar question that helped me get to the answer for your question:
(Thanks to @OMG Ponies) LINK (please read his answer to get more understanding about the issue)

Assuming that you want to count the quizzes sequentially:
Here is the SQLFiddle link to my answer.

Basically, you're counting the number of entries, and displaying that in a column.

Upvotes: 0

Wistar
Wistar

Reputation: 3790

You can either order by your primary key if you have one such as

select q.category_id, q.quiz_id, concat('Quiz ',q.name) name
from quiz q
where q.category_id = 11
order by q.id, q.category_id ASC
limit 2
offset 2;

If you don't I suggest you add a primary with something like this:

ALTER TABLE quiz ADD id INT PRIMARY KEY AUTO_INCREMENT;

It does not have to be PRIMARY if for some reasons that doesn't work with what you do. You could also use the aforementioned statement to add an rank column which is also auto_increment

Something like:

ALTER TABLE quiz ADD rank INT AUTO_INCREMENT;

(Then run the first query.)

Upvotes: 0

Related Questions