-2

I don't know it even possible to create temporary sequence in MYSQL sql query. If it is possible please advise how it is done for example

I have a column name called

Phase and data below

  • Phase A
  • Phase C
  • Phase B
  • Phase D

I want to query out and create a sequence number 1, 2, 3, 4, 5 base on phase a, b, c, d...so the query output will become

Seq     Phase
---------------
1       Phase A
2       Phase B
3       Phase C
4       Phase D
Electronic Circuit
  • 335
  • 5
  • 18
  • What are you trying to accomplish that you need a temporary auto_increment column? – topshot Sep 20 '16 at 15:28
  • I want to make a query and create a temporary sequence column base on the order by a column name alphabetical – Electronic Circuit Sep 20 '16 at 15:29
  • So column Phase contains A,C,B,D. But you want output to be 1,3,2,4 instead? – topshot Sep 20 '16 at 15:33
  • If there are relatively small amount of phase values, you could use the `IF` function or `CASE` statement. If larger amount, I'd use a lookup table that you would join to. See http://stackoverflow.com/questions/5951157/if-in-select-statement-choose-output-value-based-on-column-values among others. – topshot Sep 20 '16 at 15:39
  • That dupe target shown on the Close has a nice answer from `OMG Ponies` . – Drew Sep 20 '16 at 17:01

1 Answers1

1

Don't know whether I am answering your question properly. As I understood, you want to generate a sequence on the fly for your results set. Could help more if this is not exactly what you require.

SELECT @seq:=@seq+1 id, column_1, column_2 FROM your_table, (SELECT @seq:=0) a;
picmate 涅
  • 3,951
  • 5
  • 43
  • 52
  • No, that's not what he wants. Just to replace alpha values with numeric ones. There are many examples already here of that. – topshot Sep 20 '16 at 15:42
  • @picmate thats what i want i think topshot misunderstood my intention...but using your query there is an error says "Error 1: could not prepare statement (1 unrecognized token: ":") – Electronic Circuit Sep 20 '16 at 15:46
  • Happy to help. Are you running it from a script of some kind? If so, could you first try running it in mysql server itself? The query works perfectly for me. – picmate 涅 Sep 20 '16 at 15:49
  • @picmate i will try it tomorrow when i got back to work and see how it goes – Electronic Circuit Sep 20 '16 at 15:52
  • @rookie_coder so your "exactly" comment wasn't accurate? Perhaps you failed to mention that Phase doesn't have limited values - that it in itself is a unique sequence? – topshot Sep 20 '16 at 16:23
  • @picmate brilliant that query exactly answered my question now i have sequence column on the fly perfect thanks – Electronic Circuit Sep 21 '16 at 01:30