Search code examples
mysqlsqlphpmyadminmaxsqlfiddle

Initial zeros stripped of when using Max in mysql

To test - 

CREATE TABLE shop (
    article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
    dealer  CHAR(20)                 DEFAULT ''     NOT NULL,
    price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,
    PRIMARY KEY(article, dealer));
INSERT INTO shop VALUES
    (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
    (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

My Query -

SELECT MAX( article ) AS article
FROM shop

It outputs - 

+---------+
| article |
+---------+
|       4 |
+---------+

However I was expecting -

+---------+
| article |
+---------+
|    0004 |
+---------+

I tried to CAST but no luck.

Let me know how could I handle this.

FYI- I tried to create SQL Fiddle for this but fiddle is not filling Zeros http://sqlfiddle.com/#!2/4abbe/24 when executed SELECT * FROM shop

However in phpmyadmin its working correctly with ooo's fill.

MySQL setting - Server version: 5.5.16 PHPMYADMIN setting - Version information: 3.4.5

My table data representation -

table data

Solution 
  • SELECT  LPAD(MAX( article ), 4, '0') AS article FROM shop;

    ;-)