problem with sorting id

[boske33]

Hi to all!
i have one problem i have table like this:
ID DAT BOD

1, '19540801', 73,
2, '19540726', 75,
3, '19550801', 75,
4, '19550906', 75,
5, '19540725', 74,

and when i use this query

select id,dat,bod from test.datum order by bod

i got this :

ID DAT BOD

1, '19540801', 73
5, '19540725', 74
2, '19540726', 75
3, '19550801', 75
4, '19550906', 75

but how to make table like this:

ID DAT BOD

1, '19540801', 73
2, '19540725', 74
3, '19540726', 75
4, '19550801', 75
5, '19550906', 75

here ID column have new ID started from 1 and i need to save
column DAT and BOD intact, so need to change only ID column.How i can do this?

Tnx

 

[Kozusnik]

Not sure what you're trying to do. Are you simply trying to sort by BOD, then by ID? If so then just comma separate your fields in the ORDER BY section...

SELECT id,dat,bod FROM test.datum order by bod,id

Or are you hoping to change the data?

Mark

 

[r937]

CREATE TABLE justasec
( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
, dat CHAR(8)
, bod SMALLINT
);
INSERT INTO justasec (dat,bod)
SELECT dat,bod FROM datum ORDER BY bod,dat
;
TRUNCATE TABLE datum
;
INSERT INTO datum SELECT * FROM justasec
;
DROP TABLE justasec
;

two things:

1. it would be a good idea to back up your data first

2. any time somebody wants to mess around with id sequence numbers, invariably they are thinking of doing something that they shouldn't do

why do you want to renumber them?

r937.com | rudy.ca

 

[boske33]

Thanks r937 for help!

I modify your example and made what i need.

i made two table
justasec

DROP TABLE IF EXISTS `test`.`justasec`;
CREATE TABLE `test`.`justasec` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`dat` char(8) DEFAULT NULL,
`bod` smallint(6) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

and

justasec1

DROP TABLE IF EXISTS `test`.`justasec1`;
CREATE TABLE `test`.`justasec1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`dat` char(8) DEFAULT NULL,
`bod` smallint(6) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

and i made to insert data from justasec to justasec1 table
because in first table i cant have sorted data as i wish ,having sorted id from 1 to 10 and i need this for report.

So i have made query and it gives me what i want



in justasec i had

1, '19540801', 73
2, '19540726', 75
3, '19550801', 75
4, '19550906', 75
5, '19540725', 74

and after my query:
INSERT INTO justasec1 (dat,bod) SELECT dat,bod FROM justasec ORDER BY bod

i had this

1, '19540801', 73
2, '19540725', 74
3, '19540726', 75
4, '19550801', 75
5, '19550906', 75

as you see i had sorted list with id order from 1 to 5 and this is what i want.

Tnx