Mixing
How can I order item and subitem?
1
I have the table:
CREATE TABLE lc (
id CHARACTER(4),
name TEXT
);
The column "id" has code value where there is item and subitem, for example:
Items
1, 2, 3 ... 10, 11 ...
Subitems :
101, 102, 201, 301 ... 1001, 1002, 1101 ...
Where, 101 is subitem of 1, 201 is subitem of 2, 1001 is subitem of 10, and so on.
How can I use "Order By" and have the following order:
1
101
102
2
201
...
10
1001
1002
11
1101
...
sql postgresql select sql-order-by
edited Jan 11 at 16:37
Machavity
24.7k135981
asked Jan 2 at 19:55
Poliano MartiniPoliano Martini
234
|
show 1 more comment
1
I have the table:
CREATE TABLE lc (
id CHARACTER(4),
name TEXT
);
The column "id" has code value where there is item and subitem, for example:
Items
1, 2, 3 ... 10, 11 ...
Subitems :
101, 102, 201, 301 ... 1001, 1002, 1101 ...
Where, 101 is subitem of 1, 201 is subitem of 2, 1001 is subitem of 10, and so on.
How can I use "Order By" and have the following order:
1
101
102
2
201
...
10
1001
1002
11
1101
...
sql postgresql select sql-order-by
edited Jan 11 at 16:37
Machavity
24.7k135981
asked Jan 2 at 19:55
Poliano MartiniPoliano Martini
234
You should really change your table design and add aparentcolumn to avoid such subitem structure
– juergen d
Jan 2 at 19:56
You are showing sample data until item 11. With more items, we'll reach item 101 sometime (which already is a subitem to item10). Its first subitem would be 10101. That would be placed between 101 and 102? I.e. 1, 101, 10101, 102, …?
– Thorsten Kettner
Jan 2 at 20:17
@juergend, because is a code table of an organ of Brazil. ovalleleao.com.br/Publicacoes/…
– Poliano Martini
Jan 2 at 20:37
@ThorstenKettner No! Only two levels... The first of 10 is 1001. I already solve! Thx!
– Poliano Martini
Jan 2 at 20:39
Ah, okay, I see. The docs go up to item 40 and you think it guaranteed that item 101 will never be reached.
– Thorsten Kettner
Jan 2 at 20:50
|
show 1 more comment
1
1
1
I have the table:
CREATE TABLE lc (
id CHARACTER(4),
name TEXT
);
The column "id" has code value where there is item and subitem, for example:
Items
1, 2, 3 ... 10, 11 ...
Subitems :
101, 102, 201, 301 ... 1001, 1002, 1101 ...
Where, 101 is subitem of 1, 201 is subitem of 2, 1001 is subitem of 10, and so on.
How can I use "Order By" and have the following order:
1
101
102
2
201
...
10
1001
1002
11
1101
...
sql postgresql select sql-order-by
edited Jan 11 at 16:37
Machavity
24.7k135981
asked Jan 2 at 19:55
Poliano MartiniPoliano Martini
234
I have the table:
CREATE TABLE lc (
id CHARACTER(4),
name TEXT
);
The column "id" has code value where there is item and subitem, for example:
Items
1, 2, 3 ... 10, 11 ...
Subitems :
101, 102, 201, 301 ... 1001, 1002, 1101 ...
Where, 101 is subitem of 1, 201 is subitem of 2, 1001 is subitem of 10, and so on.
How can I use "Order By" and have the following order:
1
101
102
2
201
...
10
1001
1002
11
1101
...
sql postgresql select sql-order-by
sql postgresql select sql-order-by
edited Jan 11 at 16:37
Machavity
24.7k135981
asked Jan 2 at 19:55
Poliano MartiniPoliano Martini
234
edited Jan 11 at 16:37
Machavity
24.7k135981
asked Jan 2 at 19:55
Poliano MartiniPoliano Martini
234
edited Jan 11 at 16:37
Machavity
24.7k135981
edited Jan 11 at 16:37
Machavity
24.7k135981
edited Jan 11 at 16:37
Machavity
24.7k135981
24.7k135981
asked Jan 2 at 19:55
Poliano MartiniPoliano Martini
234
asked Jan 2 at 19:55
Poliano MartiniPoliano Martini
234
asked Jan 2 at 19:55
Poliano MartiniPoliano Martini
234
234
You should really change your table design and add aparentcolumn to avoid such subitem structure
– juergen d
Jan 2 at 19:56
You are showing sample data until item 11. With more items, we'll reach item 101 sometime (which already is a subitem to item10). Its first subitem would be 10101. That would be placed between 101 and 102? I.e. 1, 101, 10101, 102, …?
– Thorsten Kettner
Jan 2 at 20:17
@juergend, because is a code table of an organ of Brazil. ovalleleao.com.br/Publicacoes/…
– Poliano Martini
Jan 2 at 20:37
@ThorstenKettner No! Only two levels... The first of 10 is 1001. I already solve! Thx!
– Poliano Martini
Jan 2 at 20:39
Ah, okay, I see. The docs go up to item 40 and you think it guaranteed that item 101 will never be reached.
– Thorsten Kettner
Jan 2 at 20:50
|
show 1 more comment
You should really change your table design and add aparentcolumn to avoid such subitem structure
– juergen d
Jan 2 at 19:56
You are showing sample data until item 11. With more items, we'll reach item 101 sometime (which already is a subitem to item10). Its first subitem would be 10101. That would be placed between 101 and 102? I.e. 1, 101, 10101, 102, …?
– Thorsten Kettner
Jan 2 at 20:17
@juergend, because is a code table of an organ of Brazil. ovalleleao.com.br/Publicacoes/…
– Poliano Martini
Jan 2 at 20:37
@ThorstenKettner No! Only two levels... The first of 10 is 1001. I already solve! Thx!
– Poliano Martini
Jan 2 at 20:39
Ah, okay, I see. The docs go up to item 40 and you think it guaranteed that item 101 will never be reached.
– Thorsten Kettner
Jan 2 at 20:50
You should really change your table design and add a
parent column to avoid such subitem structure– juergen d
Jan 2 at 19:56
You should really change your table design and add a
parent column to avoid such subitem structure– juergen d
Jan 2 at 19:56
You are showing sample data until item 11. With more items, we'll reach item 101 sometime (which already is a subitem to item10). Its first subitem would be 10101. That would be placed between 101 and 102? I.e. 1, 101, 10101, 102, …?
– Thorsten Kettner
Jan 2 at 20:17
You are showing sample data until item 11. With more items, we'll reach item 101 sometime (which already is a subitem to item10). Its first subitem would be 10101. That would be placed between 101 and 102? I.e. 1, 101, 10101, 102, …?
– Thorsten Kettner
Jan 2 at 20:17
@juergend, because is a code table of an organ of Brazil. ovalleleao.com.br/Publicacoes/…
– Poliano Martini
Jan 2 at 20:37
@juergend, because is a code table of an organ of Brazil. ovalleleao.com.br/Publicacoes/…
– Poliano Martini
Jan 2 at 20:37
@ThorstenKettner No! Only two levels... The first of 10 is 1001. I already solve! Thx!
– Poliano Martini
Jan 2 at 20:39
@ThorstenKettner No! Only two levels... The first of 10 is 1001. I already solve! Thx!
– Poliano Martini
Jan 2 at 20:39
Ah, okay, I see. The docs go up to item 40 and you think it guaranteed that item 101 will never be reached.
– Thorsten Kettner
Jan 2 at 20:50
Ah, okay, I see. The docs go up to item 40 and you think it guaranteed that item 101 will never be reached.
– Thorsten Kettner
Jan 2 at 20:50
|
show 1 more comment
2 Answers
2
active
oldest
votes
1
You can use this:
select *
from lc
order by case when id::int < 100 then (id::int::text || '00')::int else id::int end
However, your table really needs a redesign.
answered Jan 2 at 20:10
klinklin
60.6k65787
I can not modify it because it is a code table of an organ of Brazil, which has this standard.
– Poliano Martini
Jan 2 at 20:28
Thx! This query showed the expected result.
– Poliano Martini
Jan 2 at 20:29
Whyid::int::textand notid?
– forpas
Jan 2 at 20:30
@forpas - becausecharacter(4)fills not used chars with spaces.
– klin
Jan 2 at 20:33
add a comment |
1
Your problem is that some items are one digit (1, 2, etc.), some are two (10, 11, etc.).
You want odd numbers of digits first (1 or 3 digits: 1, 2, 101, 102, 201, ...), followed by even numbers of digits (2 or 4 digits: 10, 11, 1001, 1002, 1101, ...). Inside these two sets you want text order: '101' after '1' but before '2'.
select *
from mytable
order by length(num::text) % 2 desc, num::text;
answered Jan 2 at 21:06
Thorsten KettnerThorsten Kettner
52.9k32643
Another way that also worked. Very cool. Thx!
– Poliano Martini
Jan 2 at 21:19
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54012367%2fhow-can-i-order-item-and-subitem%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
You can use this:
select *
from lc
order by case when id::int < 100 then (id::int::text || '00')::int else id::int end
However, your table really needs a redesign.
answered Jan 2 at 20:10
klinklin
60.6k65787
I can not modify it because it is a code table of an organ of Brazil, which has this standard.
– Poliano Martini
Jan 2 at 20:28
Thx! This query showed the expected result.
– Poliano Martini
Jan 2 at 20:29
Whyid::int::textand notid?
– forpas
Jan 2 at 20:30
@forpas - becausecharacter(4)fills not used chars with spaces.
– klin
Jan 2 at 20:33
add a comment |
1
You can use this:
select *
from lc
order by case when id::int < 100 then (id::int::text || '00')::int else id::int end
However, your table really needs a redesign.
answered Jan 2 at 20:10
klinklin
60.6k65787
I can not modify it because it is a code table of an organ of Brazil, which has this standard.
– Poliano Martini
Jan 2 at 20:28
Thx! This query showed the expected result.
– Poliano Martini
Jan 2 at 20:29
Whyid::int::textand notid?
– forpas
Jan 2 at 20:30
@forpas - becausecharacter(4)fills not used chars with spaces.
– klin
Jan 2 at 20:33
add a comment |
1
1
1
You can use this:
select *
from lc
order by case when id::int < 100 then (id::int::text || '00')::int else id::int end
However, your table really needs a redesign.
answered Jan 2 at 20:10
klinklin
60.6k65787
You can use this:
select *
from lc
order by case when id::int < 100 then (id::int::text || '00')::int else id::int end
However, your table really needs a redesign.
answered Jan 2 at 20:10
klinklin
60.6k65787
answered Jan 2 at 20:10
klinklin
60.6k65787
answered Jan 2 at 20:10
klinklin
60.6k65787
answered Jan 2 at 20:10
klinklin
60.6k65787
60.6k65787
I can not modify it because it is a code table of an organ of Brazil, which has this standard.
– Poliano Martini
Jan 2 at 20:28
Thx! This query showed the expected result.
– Poliano Martini
Jan 2 at 20:29
Whyid::int::textand notid?
– forpas
Jan 2 at 20:30
@forpas - becausecharacter(4)fills not used chars with spaces.
– klin
Jan 2 at 20:33
add a comment |
I can not modify it because it is a code table of an organ of Brazil, which has this standard.
– Poliano Martini
Jan 2 at 20:28
Thx! This query showed the expected result.
– Poliano Martini
Jan 2 at 20:29
Whyid::int::textand notid?
– forpas
Jan 2 at 20:30
@forpas - becausecharacter(4)fills not used chars with spaces.
– klin
Jan 2 at 20:33
I can not modify it because it is a code table of an organ of Brazil, which has this standard.
– Poliano Martini
Jan 2 at 20:28
I can not modify it because it is a code table of an organ of Brazil, which has this standard.
– Poliano Martini
Jan 2 at 20:28
Thx! This query showed the expected result.
– Poliano Martini
Jan 2 at 20:29
Thx! This query showed the expected result.
– Poliano Martini
Jan 2 at 20:29
Why
id::int::text and not id?– forpas
Jan 2 at 20:30
Why
id::int::text and not id?– forpas
Jan 2 at 20:30
@forpas - because
character(4) fills not used chars with spaces.– klin
Jan 2 at 20:33
@forpas - because
character(4) fills not used chars with spaces.– klin
Jan 2 at 20:33
add a comment |
1
Your problem is that some items are one digit (1, 2, etc.), some are two (10, 11, etc.).
You want odd numbers of digits first (1 or 3 digits: 1, 2, 101, 102, 201, ...), followed by even numbers of digits (2 or 4 digits: 10, 11, 1001, 1002, 1101, ...). Inside these two sets you want text order: '101' after '1' but before '2'.
select *
from mytable
order by length(num::text) % 2 desc, num::text;
answered Jan 2 at 21:06
Thorsten KettnerThorsten Kettner
52.9k32643
Another way that also worked. Very cool. Thx!
– Poliano Martini
Jan 2 at 21:19
add a comment |
1
Your problem is that some items are one digit (1, 2, etc.), some are two (10, 11, etc.).
You want odd numbers of digits first (1 or 3 digits: 1, 2, 101, 102, 201, ...), followed by even numbers of digits (2 or 4 digits: 10, 11, 1001, 1002, 1101, ...). Inside these two sets you want text order: '101' after '1' but before '2'.
select *
from mytable
order by length(num::text) % 2 desc, num::text;
answered Jan 2 at 21:06
Thorsten KettnerThorsten Kettner
52.9k32643
Another way that also worked. Very cool. Thx!
– Poliano Martini
Jan 2 at 21:19
add a comment |
1
1
1
Your problem is that some items are one digit (1, 2, etc.), some are two (10, 11, etc.).
You want odd numbers of digits first (1 or 3 digits: 1, 2, 101, 102, 201, ...), followed by even numbers of digits (2 or 4 digits: 10, 11, 1001, 1002, 1101, ...). Inside these two sets you want text order: '101' after '1' but before '2'.
select *
from mytable
order by length(num::text) % 2 desc, num::text;
answered Jan 2 at 21:06
Thorsten KettnerThorsten Kettner
52.9k32643
Your problem is that some items are one digit (1, 2, etc.), some are two (10, 11, etc.).
You want odd numbers of digits first (1 or 3 digits: 1, 2, 101, 102, 201, ...), followed by even numbers of digits (2 or 4 digits: 10, 11, 1001, 1002, 1101, ...). Inside these two sets you want text order: '101' after '1' but before '2'.
select *
from mytable
order by length(num::text) % 2 desc, num::text;
answered Jan 2 at 21:06
Thorsten KettnerThorsten Kettner
52.9k32643
edited Jan 2 at 21:18
answered Jan 2 at 21:06
Thorsten KettnerThorsten Kettner
52.9k32643
answered Jan 2 at 21:06
Thorsten KettnerThorsten Kettner
52.9k32643
answered Jan 2 at 21:06
Thorsten KettnerThorsten Kettner
52.9k32643
52.9k32643
Another way that also worked. Very cool. Thx!
– Poliano Martini
Jan 2 at 21:19
add a comment |
Another way that also worked. Very cool. Thx!
– Poliano Martini
Jan 2 at 21:19
Another way that also worked. Very cool. Thx!
– Poliano Martini
Jan 2 at 21:19
Another way that also worked. Very cool. Thx!
– Poliano Martini
Jan 2 at 21:19
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54012367%2fhow-can-i-order-item-and-subitem%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
You should really change your table design and add a
parentcolumn to avoid such subitem structure– juergen d
Jan 2 at 19:56
You are showing sample data until item 11. With more items, we'll reach item 101 sometime (which already is a subitem to item10). Its first subitem would be 10101. That would be placed between 101 and 102? I.e. 1, 101, 10101, 102, …?
– Thorsten Kettner
Jan 2 at 20:17
@juergend, because is a code table of an organ of Brazil. ovalleleao.com.br/Publicacoes/…
– Poliano Martini
Jan 2 at 20:37
@ThorstenKettner No! Only two levels... The first of 10 is 1001. I already solve! Thx!
– Poliano Martini
Jan 2 at 20:39
Ah, okay, I see. The docs go up to item 40 and you think it guaranteed that item 101 will never be reached.
– Thorsten Kettner
Jan 2 at 20:50