4

SO,

I have an issue about query in MySQL DBMS. I have table repeats with data, like:

+--------+-----+
| record | num |
+--------+-----+
|  foo   |  2  |
+--------+-----+
|  bar   |  3  |
+--------+-----+
|  baz   |  1  |
+--------+-----+

here record field contains some data (irrelevant, let it be simple string) and num field contains number of repeats of current row in the result set. So, I want to result in:

foo
foo
bar
bar
bar
baz

i.e. 'foo' was repeated twice, 'bar' - three times and 'baz' - once.

Ok, now what I've tried. I've created a table:

CREATE TABLE `natural` (
 `id` int(11) unsigned NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=InnoDB

-then filled it with natural numbers, from 1 till MAX(num) from repeats table. After this, I can achieve my goal via:

SELECT 
  `repeats`.record 
FROM 
  `natural` 
    LEFT JOIN `repeats` 
      ON `natural`.id<=`repeats`.num
WHERE 
  `repeats`.record IS NOT NULL

-this works well, but I don't want to use natural table. And so, my question is: is it possible to achieve my goal with repetition of rows in a single SQL-query, without creating temporary data-structure? (Since it is MySQL, tricks with variables will also fit - I just have no idea now of how to do that). However, answer 'no, this is impossible because ..' will also be welcome.

I also suspect that it is some 'common' problem, but have not found any corresponding info, unfortunately. Update: here is a similar problem (yet in the same time - not fully similar since it also upcomes to sequence generation problem which I want to avoid)

Community
  • 1
  • 1
Alma Do
  • 37,009
  • 9
  • 76
  • 105
  • actually I think you can just create and leave this table with numbers in your db, it's the fastest way to create sequence of numbers as far as I know. – Roman Pekar Sep 04 '13 at 07:27
  • @RomanPekar but I've mentioned that. My question is - how to avoid that (and, ok - let it be not the 'fastest' way) – Alma Do Sep 04 '13 at 07:29
  • I think there's no way you can do it in MySQL if you dont't have a table with guaranteed amount of rows. If you have such a table, you can generate sequence by `select @rownum:=@rownum+1 n, t.* from tbl t, (SELECT @rownum:=0) r` and then cross join it a number of times - looks very alike as creating view and cross join it to yourself several time. – Roman Pekar Sep 04 '13 at 07:45
  • @RomanPekar - nevermind now :\ Thanks. Somehow my question was marked as duplicated of irrelevant question (that one has no solution for my problem, important part of which is _"resolve without temporary structures"_). Ah, sometimes I'm stuck with SO :\ – Alma Do Sep 04 '13 at 07:48
  • 2
    http://sqlfiddle.com/#!2/e579c/1 – Justin Sep 04 '13 at 07:50
  • @AlmaDoMundo as an acacademic task it can be solved without temp structures - just inner join with subquery of union 1, 2, 3, 4 and so on :) but as practical it's better to go with table of number from 1 to some large number with primary key by this number – Roman Pekar Sep 04 '13 at 07:52
  • @Justin, wow, **looks interesting** - may be that I'm seeking for :) – Alma Do Sep 04 '13 at 07:55

0 Answers0