Sort rows in database Mysql

Question

I've database with a lot of entries and with help of some friends here i manage how to remove the duplicates lines deleting-duplicate-rows-in-a-mysql-database but now i've to face new problem with is unsorted rows.

here is example of my database now

CREATE TABLE `my_table` (
  `id` int(10) NOT NULL default '0',
  `name` varchar(255) NOT NULL default '',
  `address` varchar(255) NOT NULL default '',
  `phone` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `my_table` VALUES (784, 'Albert', 'EGYPT', '202020');
INSERT INTO `my_table` VALUES (21, 'John', 'USA', '984731');
INSERT INTO `my_table` VALUES (56, 'Albert', 'EGYPT', '343354');

as you can see at id [784,21,56] so is there any how i can resort it to be

INSERT INTO `my_table` VALUES (1, 'Albert', 'EGYPT', '202020');
INSERT INTO `my_table` VALUES (2, 'John', 'USA', '984731');
INSERT INTO `my_table` VALUES (3, 'Albert', 'EGYPT', '343354');

id [1,2,3,4,....etc] resort it , I'm aware that change in ID may cause problems in script itself since it depends on id to garb the entry informations but since it is new then i need to sort it 1,2,3,4,.....etc.

Fred · Accepted Answer · 2011-12-30 14:06:28Z

1

What about puting an auto increment on your table?

CREATE TABLE `my_table` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL default '',
  `address` varchar(255) NOT NULL default '',
  `phone` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Then you just need to insert values without specifying ids.

[EDIT]

You could also order your rows like this without changing anything in your table structure

select @rownum:=@rownum+1 as rank, t.*
from my_table t, (SELECT @rownum:=0) r
order by rank;

edited Dec 30, 2011 at 14:06

answered Dec 30, 2011 at 13:50

Fred

5,0361 gold badge26 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Florin Frătică · Accepted Answer · 2011-12-30 13:50:36Z

1

I think if you delete the ID field and create it again (with autoincrement) this will fix your problem. But I never checked this so first make a backup of your database.

answered Dec 30, 2011 at 13:50

Florin Frătică

5874 gold badges7 silver badges13 bronze badges

Comments

Grim... · Accepted Answer · 2011-12-30 13:50:59Z

1

If you're sure you want to do it, I'm pretty sure removing the id field and adding it again (indexed and with autoincrement) will renumber the rows.

I'm not sure, though, because it's not the kind of thing I've ever done. Are you sure you want to do this? What does it matter if they're numbered strangely?

Also, there's no guarantee that MySQL will renumber the rows the way you want - it might well jumble them up a bit.

If you want them ordered by country, or something, you'd have to use some PHP and update the rows one by one, which isn't as hard as it might sound.

answered Dec 30, 2011 at 13:50

Grim...

17k7 gold badges47 silver badges62 bronze badges

Comments

Michał Powaga · Accepted Answer · 2011-12-30 13:51:57Z

1

Use AUTO_INCREMENT column:

CREATE TABLE `my_table` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL default '',
  `address` varchar(255) NOT NULL default '',
  `phone` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

and then insert:

INSERT INTO `my_table` VALUES ('Albert', 'EGYPT', '202020');
INSERT INTO `my_table` VALUES ('John', 'USA', '984731');
INSERT INTO `my_table` VALUES ('Albert', 'EGYPT', '343354');

answered Dec 30, 2011 at 13:51

Michał Powaga

23.4k8 gold badges55 silver badges62 bronze badges

1 Comment

Reham Fahmy Over a year ago

Impossible! for 150K entries to do this got to remove the already exist un-ordered ID !

Community · Accepted Answer · 2017-05-23 12:27:39Z

1

this question ( Reorder the database record rows through front-end using php ) does exactly what you want, although it uses php to accomplish the task.

grts,

M.

edited May 23, 2017 at 12:27

CommunityBot

11 silver badge

answered Dec 30, 2011 at 13:53

Mikey.

376 bronze badges

Collectives™ on Stack Overflow

Sort rows in database Mysql

5 Answers 5

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related