ORDER BY question

shawjames

I am trying to sort my sql query, like this:

3 fields: ID, Name and Set.. which means it is part of another row (linked to ID). If Set is 0, it is not part of another row (unlinked)

ID,NAME,SET
1,A Test,0
2,B Test,0
3,C Test,0
4,D Test,2
5,E Test,2
6,F Test,0

I want to sort by NAME, but I want to keep all linked rows sorted just under the row they are linked to. For example the result would be:

1,A Test,0
2,B Test,0
4,D Test,2
5,E Test,2
3,C Test,0
6,F Test,0

Is there a way to do something like this with one Query?

djjjozsi

you want to sort with Name, and the 2. table's value

this is in one query.

SELECT field1.ID, field1.NAME, field1.SET, field2.value
FROM field2
INNER JOIN field1 ON field2.field1_id = field1.ID
ORDER BY field1.NAME, field2.value;

shawjames;10893213 wrote:
I am trying to sort my sql query, like this:

3 fields: ID, Name and Set.. which means it is part of another row (linked to ID). If Set is 0, it is not part of another row (unlinked)

ID,NAME,SET
1,A Test,0
2,B Test,0
3,C Test,0
4,D Test,2
5,E Test,2
6,F Test,0

I want to sort by NAME, but I want to keep all linked rows sorted just under the row they are linked to. For example the result would be:

1,A Test,0
2,B Test,0
4,D Test,2
5,E Test,2
3,C Test,0
6,F Test,0

Is there a way to do something like this with one Query?

shawjames

djjjozsi;10893225 wrote:
you want to sort with Name, and the 2. table's value

this is in one query.

SELECT field1.ID, field1.NAME, field1.SET, field2.value
FROM field2
INNER JOIN field1 ON field2.field1_id = field1.ID
ORDER BY field1.NAME, field2.value;

I was not able to get this to work. Okay, this is in table: p_carts

The 3 fields are: cID, pName, pSetItem

I want to sort the query by pName, unless the pSetItem value matches the cID field. These will be listed just after the row with the matching cID

So, if this was the rows:

cID,pName,pSetItem
1,A,0
2,B,0
3,C,0
4,D,2
5,E,0
6,F,2
7,G,0

The sorted list would be like this:
1,A,0
2,B,0
4,D,2 <<these sorted after the cID=2 row, like a sub section of all the pSetItem=2
6,F,2
3,C,0
5,E,0
7,G,0

Given these field names, how would the query be formatted?

leatherback

It sounds like what you are doing is storing hierarchical data. The way you have organized your table does not allow for easy retrieval of the tree. One way to do it is with an hierarchical tree traversal routine (http://www.alandelevie.com/2008/07/12/recursion-less-storage-of-hierarchical-data-in-a-relational-database/).

You could try using concat and a self-joining tables while renaming the tables. however, this will prove to be terribly slow for larger tables.

djjjozsi

if you store these parent-child in another table, your life would be better.

// i laught from this video about 20 minutes 🙂 http://www.youtube.com/watch?v=mNfMD3mafoQ&feature=rec-HM-fresh+div

its name is: two drunky person bring a wood

shawjames

Thank you, I wanted to see if there was a slick way to do it with a query... but I suppose the best method would be to: add another column that would show the sort order, run a simple PHP script to assign the correct sort number to that new field... and then simply sort by that field.

This table does have about 500,000 rows.

Would this be the most efficient way to accomplish this?

leatherback;10893303 wrote:
It sounds like what you are doing is storing hierarchical data. The way you have organized your table does not allow for easy retrieval of the tree. One way to do it is with an hierarchical tree traversal routine (http://www.alandelevie.com/2008/07/12/recursion-less-storage-of-hierarchical-data-in-a-relational-database/).

You could try using concat and a self-joining tables while renaming the tables. however, this will prove to be terribly slow for larger tables.

Sxooter

smarlowe=# create table test (id int, s int, info text);
CREATE TABLE
smarlowe=# insert into test values (1,0,'abc');
INSERT 0 1
smarlowe=# insert into test values (2,0,'def');
INSERT 0 1
smarlowe=# insert into test values (3,0,'gh');
INSERT 0 1
smarlowe=# insert into test values (4,2,'jj');
INSERT 0 1
smarlowe=# insert into test values (5,2,'jj');
INSERT 0 1
smarlowe=# insert into test values (6,0,'kk');
INSERT 0 1
smarlowe=# select * from test order by case when s <>0 then s else id end;
id | s | info
----+---+------
1 | 0 | abc
2 | 0 | def
4 | 2 | jj
5 | 2 | jj
3 | 0 | gh
6 | 0 | kk
(6 rows)

Sxooter

Note that my method could put the other rows with s<>0 ahead of the parent row. You'll need a fancier case statement to make the row where s=0 come ahead of the ones where s = that rows id... If you know what I mean.