1

I have a table like this

CREATE TABLE `Table1` (
`id` INT NOT NULL AUTO_INCREMENT,
`parent` INT NOT NULL,
`comment` TEXT,
PRIMARY KEY(`id`)
);

Which has following sample data (with some user generated text in comment field)

+------+---------+-
| id   | parent  |
+------+---------+
| 1    |    0    |
| 2    |    1    |
| 3    |    0    |
| 4    |    1    |
| 5    |    0    |
| 6    |    5    |
| 7    |    4    |
+------+---------+

How can I get following order in SELECT statement. 

+------+---------+-
| id   | parent  |
+------+---------+
| 1    |    0    |
| 2    |    1    |
| 4    |    1    |
| 7    |    4    |
| 3    |    0    |
| 5    |    0    |
| 6    |    5    |
+------+---------+

I want to list a record after its parent record, and rows with parent 0 should be sorted on basis of their 'id'.

flavio.donze
  • 7,432
  • 9
  • 58
  • 91
sandy
  • 21
  • 3
  • Not clear could you explain a bit more about the sorting logic you are after ? – Abhik Chakraborty Apr 21 '16 at 06:55
  • 1
    Are you sure you don't want rows with parent 0 on top, and then sorted by id, like the rest of your rows? I can't see how this ordering could be useful. If you apply a different sorting rule depending on the value... then it is not really sorted – Basile Perrenoud Apr 21 '16 at 06:57
  • 1
    MySQL is not designed to do these kind of things, better to do this after you have extracted the data by looping through the array. – Jester Apr 21 '16 at 07:06
  • I agree with the previous commenters. The SQL sort order logic would expect there is an explicit value by which the results should be ordered. In the latter `select` there is nothing like that. And btw, I think the table structure is also kind of risky or unclear; if the `parent` value can't be null and it points to the same table, what is the parent value of the first inserted row? (I know there's no referential integrity defined but that `0` parent reference might cause you trouble later when there is no row with that id.) – MJV Apr 21 '16 at 07:16
  • @ AbhikChakraborty I want to sort the comments posted by site users based on under which comment they have posted it. @Toaster I am sure "I do not want all rows with parent 0 together and and on the begining". @ Jester I am not sure if you are correct. I am just looking for solutions. @ MJV a record can have parent as 0, if it has no parent. – sandy Apr 21 '16 at 08:21
  • @pin, did you insist id's with parent 0 to be 0, but not null? – mitkosoft Apr 21 '16 at 08:25
  • @mitkosoft nope. Logically for me it is ok if parent in 0 or null. – sandy Apr 21 '16 at 08:26

1 Answers1

1

So if I understand correctly, your comments are structured as a tree (root is 0, others all have a parent and are ultimately linked to 0). I don't know which sql tech you use, but Oracle provides a way to display the tree elements in order.

Look for "start with" and "connect by", for example here: http://www.adp-gmbh.ch/ora/sql/connect_by.html

I think this could help you achieve what you want to do

Something like (not tested at all, just a hint)

select * from Table1 start with 0 connect by parent

EDIT

Since You actually use MySQL, the above solution is not possible (Thanks @MJV for noticing). Here is an idea of a solution. It assumes that you can add more info in your table when inserting a new comment. It also assume that a new comment will have a higher ID than its parents (for sorting easily. Your example seems to assume the same thing)

You could add the root_parent (highest parent) for each comment in your table:

+------+---------+-------------+
| id   | parent  | root_parent |             Corresponding trees
+------+---------+-------------+
| 1    |    0    |      1      |         1         3          5
| 2    |    1    |      1      |        / \                  / 
| 3    |    0    |      3      |       /   \                /  
| 4    |    1    |      1      |      2     4              6 
| 5    |    0    |      5      |           / 
| 6    |    5    |      5      |          /
| 7    |    4    |      1      |         7
+------+---------+ ------------+

Now, you can get your ordering by calling

select * from table1 order by root_parent, id

Let me know if it worked

Basile Perrenoud
  • 4,039
  • 3
  • 29
  • 52
  • And you can have a look here for equivalent if you don't use oracle: http://stackoverflow.com/questions/2200636/oracle-connect-by-clause-equivalent-in-sql-server – Basile Perrenoud Apr 21 '16 at 09:45
  • I think MySQL (which the question was about) doesn't support any kind of recursive queries. More here: http://stackoverflow.com/questions/7631048/connect-by-prior-equivalent-for-mysql – MJV Apr 21 '16 at 10:10