1

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 |
+-------------+---------+--------------+
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
mwafi
  • 3,946
  • 8
  • 56
  • 83
  • 2
    You need to have another field to `order by` to determine the correct order. Sql is stored unordered -- so you won't be able to guarantee the results unless you can order by another field. – sgeddes Mar 04 '16 at 18:46
  • 2
    This would be **much** easier in anything other than MySql, because for some reason MySql doesn't have windowing functions, even though they've been part of the ansi standard for more than 10 years. With a windowing function, this is a simple `row_number()` call, which is supported in Postgresql, Oracle, Sql Server, DB2, sybase, and anything else I can think of... but not mysql. – Joel Coehoorn Mar 04 '16 at 18:46
  • 1
    There is no primary key on this `quiz` table? – Jorge Campos Mar 04 '16 at 18:50
  • Possible duplicate of [Row Rank in a MySQL View](http://stackoverflow.com/questions/1964811/row-rank-in-a-mysql-view) – Peter Mortensen Mar 05 '16 at 08:51

3 Answers3

2

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;
sgeddes
  • 62,311
  • 6
  • 61
  • 83
0

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.)

Community
  • 1
  • 1
Wistar
  • 3,770
  • 4
  • 45
  • 70
0

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.

Community
  • 1
  • 1
goodguy5
  • 422
  • 3
  • 16