CODEWITHSUNDEEP
mysqlsequenceconcatenation
nunu
nunu

Reputation: 2871

MYSQL: change current id value become sequence id each day

I want to show id value from the record below :

id      name            date
24811   Foo             2011-04-26
24812   Bar             2011-04-26
24813   Bars            2011-04-27

become this format:

date + Q + 4 digit ID sequence start from first ~ last

and the result is:

id              name            date
20110426Q0001   Foo             2011-04-26
20110426Q0002   Bar             2011-04-26
20110427Q0001   Bars            2011-04-27

But i'm just have a half query, how to make the 4 digit?

SELECT REPLACE( CONCAT( DATE( Inspection_datetime ) ,'Q',/*missing this query..*/),'-','') AS id,
       name,date
FROM mytable

after try answer below :

row @g:= `date`         id              Model           Serial      Lot_no  Inspection_datetime
1   2011-01-31 07:44:21 20110131Q0001   KW-HDR81BTJD    176X0099    010A    2011-01-31 07:44:21
1   2011-01-31 08:31:31 20110131Q0001   KW-HDR81BTJD    176X0098    010A    2011-01-31 08:31:31
1   2011-01-31 08:34:57 20110131Q0001   KW-HDR81BTJD    176X0065    010A    2011-01-31 08:34:57
1   2011-01-31 08:46:43 20110131Q0001   KW-HDR81BTJD    176X0050    010A    2011-01-31 08:46:43

Upvotes: 0

Views: 129

Answers (2)

nunu
nunu

Reputation: 2871

I don't know why both query from Khalid doesn't work in my Mysql. Even I have tried at sqlfiddle it works but it doesn't in my database. After a week i'm going crazy for this problem, i get the answer:

I'm using the second answer from Khalid then try to remove:

cross join (select @g=null, @r:= 0) t

then add before select:

set @g=null,@r:= 0;

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You con do by using user defined variables

select 
@r:= case when @g = date then @r +1 else 1 end rownum,
@g:= date,
REPLACE( CONCAT( 
  DATE(date) ,
  'Q'
  ,lpad(@r,4,'0')
  ),'-','') AS id,
name,
date
from mytable
cross join (select @g=null, @r:= 0) t
order by date

DEMO

If you are concerned to only the date part not the time part then you can do so

select 
@r:= case when @g = date(date) then @r +1 else 1 end rownum,
@g:= date(date),
REPLACE( CONCAT( 
  DATE(date) ,
  'Q'
  ,lpad(@r,4,'0')
  ),'-','') AS id,
name,
date
from mytable
cross join (select @g=null, @r:= 0) t
order by date

DEMO

Upvotes: 1

Related Questions