How to sort categorical values by an arbitrary order?
0
I have a table called ANIMALS which looks like this
| name | category |
------------------------
| fluffer | CAT |
| tooter | ELEPHANT |
| tooter_jr | ELEPHANT |
| woofer | DOG |
If I sort the table by category then I will get all CATs then DOGs then ELEPHANTs. But I need to order the table such that the sequence is CAT > ELEPHANT > DOG
How can I achieve this result?
oracle oracle-12c
asked 17 hours ago
Souradeep NandaSouradeep Nanda
395137
add a comment |
0
I have a table called ANIMALS which looks like this
| name | category |
------------------------
| fluffer | CAT |
| tooter | ELEPHANT |
| tooter_jr | ELEPHANT |
| woofer | DOG |
If I sort the table by category then I will get all CATs then DOGs then ELEPHANTs. But I need to order the table such that the sequence is CAT > ELEPHANT > DOG
How can I achieve this result?
oracle oracle-12c
asked 17 hours ago
Souradeep NandaSouradeep Nanda
395137
Create a table (category - sorting weight) and use it in the query. Or use any trick (expression likeORDER BY INSTR('CAT,ELEPHANT,DOG', category,1,1)).
– Akina
16 hours ago
add a comment |
0
0
0
I have a table called ANIMALS which looks like this
| name | category |
------------------------
| fluffer | CAT |
| tooter | ELEPHANT |
| tooter_jr | ELEPHANT |
| woofer | DOG |
If I sort the table by category then I will get all CATs then DOGs then ELEPHANTs. But I need to order the table such that the sequence is CAT > ELEPHANT > DOG
How can I achieve this result?
oracle oracle-12c
asked 17 hours ago
Souradeep NandaSouradeep Nanda
395137
I have a table called ANIMALS which looks like this
| name | category |
------------------------
| fluffer | CAT |
| tooter | ELEPHANT |
| tooter_jr | ELEPHANT |
| woofer | DOG |
If I sort the table by category then I will get all CATs then DOGs then ELEPHANTs. But I need to order the table such that the sequence is CAT > ELEPHANT > DOG
How can I achieve this result?
oracle oracle-12c
oracle oracle-12c
asked 17 hours ago
Souradeep NandaSouradeep Nanda
395137
asked 17 hours ago
Souradeep NandaSouradeep Nanda
395137
asked 17 hours ago
Souradeep NandaSouradeep Nanda
395137
asked 17 hours ago
Souradeep NandaSouradeep Nanda
395137
asked 17 hours ago
Souradeep NandaSouradeep Nanda
395137
395137
Create a table (category - sorting weight) and use it in the query. Or use any trick (expression likeORDER BY INSTR('CAT,ELEPHANT,DOG', category,1,1)).
– Akina
16 hours ago
add a comment |
Create a table (category - sorting weight) and use it in the query. Or use any trick (expression likeORDER BY INSTR('CAT,ELEPHANT,DOG', category,1,1)).
– Akina
16 hours ago
Create a table (category - sorting weight) and use it in the query. Or use any trick (expression like
ORDER BY INSTR('CAT,ELEPHANT,DOG', category,1,1)).– Akina
16 hours ago
Create a table (category - sorting weight) and use it in the query. Or use any trick (expression like
ORDER BY INSTR('CAT,ELEPHANT,DOG', category,1,1)).– Akina
16 hours ago
add a comment |
1 Answer
1
active
oldest
votes
1
This can be done using a CASE statement in the ORDER BY clause:
WITH
Animals (name, category) AS (
SELECT 'fluffer', 'CAT' FROM DUAL UNION ALL
SELECT 'tooter', 'ELEPHANT' FROM DUAL UNION ALL
SELECT 'tooter_jr', 'ELEPHANT' FROM DUAL UNION ALL
SELECT 'woofer', 'DOG' FROM DUAL
)
SELECT
name, category
FROM Animals
ORDER BY
CASE category
WHEN 'CAT' THEN 1
WHEN 'ELEPHANT' THEN 2
WHEN 'DOG' THEN 3
END
;
Please comment, if and as this requires adjustment / further detail.
answered 16 hours ago
AbeceeAbecee
31613
I am gettingerror: Error: ORA-00904: "DOG": invalid identifierI am still doing a little trial and error.
– Souradeep Nanda
16 hours ago
Oh ok. I was doing ``` ORDER BY CASE category WHEN ${TYPES.CAT} THEN 1 WHEN ${TYPES.ELEPHANT} THEN 2 WHEN ${TYPES.DOG} THEN 3 END ; ``` instead of ``` ORDER BY CASE category WHEN '${TYPES.CAT}' THEN 1 WHEN '${TYPES.ELEPHANT}' THEN 2 WHEN '${TYPES.DOG}' THEN 3 END ; ```
– Souradeep Nanda
16 hours ago
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f227461%2fhow-to-sort-categorical-values-by-an-arbitrary-order%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
This can be done using a CASE statement in the ORDER BY clause:
WITH
Animals (name, category) AS (
SELECT 'fluffer', 'CAT' FROM DUAL UNION ALL
SELECT 'tooter', 'ELEPHANT' FROM DUAL UNION ALL
SELECT 'tooter_jr', 'ELEPHANT' FROM DUAL UNION ALL
SELECT 'woofer', 'DOG' FROM DUAL
)
SELECT
name, category
FROM Animals
ORDER BY
CASE category
WHEN 'CAT' THEN 1
WHEN 'ELEPHANT' THEN 2
WHEN 'DOG' THEN 3
END
;
Please comment, if and as this requires adjustment / further detail.
answered 16 hours ago
AbeceeAbecee
31613
I am gettingerror: Error: ORA-00904: "DOG": invalid identifierI am still doing a little trial and error.
– Souradeep Nanda
16 hours ago
Oh ok. I was doing ``` ORDER BY CASE category WHEN ${TYPES.CAT} THEN 1 WHEN ${TYPES.ELEPHANT} THEN 2 WHEN ${TYPES.DOG} THEN 3 END ; ``` instead of ``` ORDER BY CASE category WHEN '${TYPES.CAT}' THEN 1 WHEN '${TYPES.ELEPHANT}' THEN 2 WHEN '${TYPES.DOG}' THEN 3 END ; ```
– Souradeep Nanda
16 hours ago
add a comment |
1
This can be done using a CASE statement in the ORDER BY clause:
WITH
Animals (name, category) AS (
SELECT 'fluffer', 'CAT' FROM DUAL UNION ALL
SELECT 'tooter', 'ELEPHANT' FROM DUAL UNION ALL
SELECT 'tooter_jr', 'ELEPHANT' FROM DUAL UNION ALL
SELECT 'woofer', 'DOG' FROM DUAL
)
SELECT
name, category
FROM Animals
ORDER BY
CASE category
WHEN 'CAT' THEN 1
WHEN 'ELEPHANT' THEN 2
WHEN 'DOG' THEN 3
END
;
Please comment, if and as this requires adjustment / further detail.
answered 16 hours ago
AbeceeAbecee
31613
I am gettingerror: Error: ORA-00904: "DOG": invalid identifierI am still doing a little trial and error.
– Souradeep Nanda
16 hours ago
Oh ok. I was doing ``` ORDER BY CASE category WHEN ${TYPES.CAT} THEN 1 WHEN ${TYPES.ELEPHANT} THEN 2 WHEN ${TYPES.DOG} THEN 3 END ; ``` instead of ``` ORDER BY CASE category WHEN '${TYPES.CAT}' THEN 1 WHEN '${TYPES.ELEPHANT}' THEN 2 WHEN '${TYPES.DOG}' THEN 3 END ; ```
– Souradeep Nanda
16 hours ago
add a comment |
1
1
1
This can be done using a CASE statement in the ORDER BY clause:
WITH
Animals (name, category) AS (
SELECT 'fluffer', 'CAT' FROM DUAL UNION ALL
SELECT 'tooter', 'ELEPHANT' FROM DUAL UNION ALL
SELECT 'tooter_jr', 'ELEPHANT' FROM DUAL UNION ALL
SELECT 'woofer', 'DOG' FROM DUAL
)
SELECT
name, category
FROM Animals
ORDER BY
CASE category
WHEN 'CAT' THEN 1
WHEN 'ELEPHANT' THEN 2
WHEN 'DOG' THEN 3
END
;
Please comment, if and as this requires adjustment / further detail.
answered 16 hours ago
AbeceeAbecee
31613
This can be done using a CASE statement in the ORDER BY clause:
WITH
Animals (name, category) AS (
SELECT 'fluffer', 'CAT' FROM DUAL UNION ALL
SELECT 'tooter', 'ELEPHANT' FROM DUAL UNION ALL
SELECT 'tooter_jr', 'ELEPHANT' FROM DUAL UNION ALL
SELECT 'woofer', 'DOG' FROM DUAL
)
SELECT
name, category
FROM Animals
ORDER BY
CASE category
WHEN 'CAT' THEN 1
WHEN 'ELEPHANT' THEN 2
WHEN 'DOG' THEN 3
END
;
Please comment, if and as this requires adjustment / further detail.
answered 16 hours ago
AbeceeAbecee
31613
answered 16 hours ago
AbeceeAbecee
31613
answered 16 hours ago
AbeceeAbecee
31613
answered 16 hours ago
AbeceeAbecee
31613
31613
I am gettingerror: Error: ORA-00904: "DOG": invalid identifierI am still doing a little trial and error.
– Souradeep Nanda
16 hours ago
Oh ok. I was doing ``` ORDER BY CASE category WHEN ${TYPES.CAT} THEN 1 WHEN ${TYPES.ELEPHANT} THEN 2 WHEN ${TYPES.DOG} THEN 3 END ; ``` instead of ``` ORDER BY CASE category WHEN '${TYPES.CAT}' THEN 1 WHEN '${TYPES.ELEPHANT}' THEN 2 WHEN '${TYPES.DOG}' THEN 3 END ; ```
– Souradeep Nanda
16 hours ago
add a comment |
I am gettingerror: Error: ORA-00904: "DOG": invalid identifierI am still doing a little trial and error.
– Souradeep Nanda
16 hours ago
Oh ok. I was doing ``` ORDER BY CASE category WHEN ${TYPES.CAT} THEN 1 WHEN ${TYPES.ELEPHANT} THEN 2 WHEN ${TYPES.DOG} THEN 3 END ; ``` instead of ``` ORDER BY CASE category WHEN '${TYPES.CAT}' THEN 1 WHEN '${TYPES.ELEPHANT}' THEN 2 WHEN '${TYPES.DOG}' THEN 3 END ; ```
– Souradeep Nanda
16 hours ago
I am getting
error: Error: ORA-00904: "DOG": invalid identifier I am still doing a little trial and error.– Souradeep Nanda
16 hours ago
I am getting
error: Error: ORA-00904: "DOG": invalid identifier I am still doing a little trial and error.– Souradeep Nanda
16 hours ago
Oh ok. I was doing ``` ORDER BY CASE category WHEN ${TYPES.CAT} THEN 1 WHEN ${TYPES.ELEPHANT} THEN 2 WHEN ${TYPES.DOG} THEN 3 END ; ``` instead of ``` ORDER BY CASE category WHEN '${TYPES.CAT}' THEN 1 WHEN '${TYPES.ELEPHANT}' THEN 2 WHEN '${TYPES.DOG}' THEN 3 END ; ```
– Souradeep Nanda
16 hours ago
Oh ok. I was doing ``` ORDER BY CASE category WHEN ${TYPES.CAT} THEN 1 WHEN ${TYPES.ELEPHANT} THEN 2 WHEN ${TYPES.DOG} THEN 3 END ; ``` instead of ``` ORDER BY CASE category WHEN '${TYPES.CAT}' THEN 1 WHEN '${TYPES.ELEPHANT}' THEN 2 WHEN '${TYPES.DOG}' THEN 3 END ; ```
– Souradeep Nanda
16 hours ago
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f227461%2fhow-to-sort-categorical-values-by-an-arbitrary-order%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
Create a table (category - sorting weight) and use it in the query. Or use any trick (expression like
ORDER BY INSTR('CAT,ELEPHANT,DOG', category,1,1)).– Akina
16 hours ago