How to sort these records?

Databases
1

Hi! I am building a file/folder tree. I have

id, name, parentid, foldertype;
1, root, 0, 1;
2, folderA, 1, 1;
3, folderB, 1, 1;
4, fileA, 2, 0;
5, fileB, 2, 0;

Foldertype =1 is folder, 0 is file. Because fileA and fileB belongs to folderA which is parentid = 2, I want to have it appear immediately after folder A. ie. I want to have something like this:

1, root, 0, 1;
2, folderA, 1, 1;
4, fileA, 2, 0;
5, fileB, 2, 0;
3, folderB, 1, 1;

Is it possible? How? Thanks.

2

imho I think you will be much better off in the long run if you use the nested set model rather than the adjacency list model you are currently using - but that’s just me.

the above link has the scripts which should help for both models.

if some time down the track you will want to move nodes and all their children to a new node I have some scripts posted in this thread which could help if you use the nested set model.

3

What you are trying to achieve should be handled in the application language not SQL.

4

thanks.