Unexpected Result SQL ORDER BY The Next CEO of Stack OverflowHow can I prevent SQL injection in PHP?Add a column with a default value to an existing table in SQL ServerInserting multiple rows in a single SQL query?Insert results of a stored procedure into a temporary tableHow do I UPDATE from a SELECT in SQL Server?Finding duplicate values in a SQL tableWhat are the options for storing hierarchical data in a relational database?How to order by with unionHow to import an SQL file using the command line in MySQL?Convert INT to VARCHAR SQL
Audio Conversion With ADS1243
What difference does it make using sed with/without whitespaces?
Does the Idaho Potato Commission associate potato skins with healthy eating?
Towers in the ocean; How deep can they be built?
Physiological effects of huge anime eyes
Small nick on power cord from an electric alarm clock, and copper wiring exposed but intact
Why did early computer designers eschew integers?
TikZ: How to fill area with a special pattern?
Purpose of level-shifter with same in and out voltages
Scary film where a woman has vaginal teeth
free fall ellipse or parabola?
How to get the last not-null value in an ordered column of a huge table?
Which one is the true statement?
Traveling with my 5 year old daughter (as the father) without the mother from Germany to Mexico
Is dried pee considered dirt?
Airplane gently rocking its wings during whole flight
Is there an equivalent of cd - for cp or mv
Is a distribution that is normal, but highly skewed, considered Gaussian?
Could a dragon use its wings to swim?
Can Sneak Attack be used when hitting with an improvised weapon?
Can I board the first leg of the flight without having final country's visa?
Are the names of these months realistic?
Decide between Polyglossia and Babel for LuaLaTeX in 2019
Is it professional to write unrelated content in an almost-empty email?
Unexpected Result SQL ORDER BY
The Next CEO of Stack OverflowHow can I prevent SQL injection in PHP?Add a column with a default value to an existing table in SQL ServerInserting multiple rows in a single SQL query?Insert results of a stored procedure into a temporary tableHow do I UPDATE from a SELECT in SQL Server?Finding duplicate values in a SQL tableWhat are the options for storing hierarchical data in a relational database?How to order by with unionHow to import an SQL file using the command line in MySQL?Convert INT to VARCHAR SQL
I have a scenario that I dont know how to get the correct results due to my lack of knowledge with SQL and need assistance.
I understand what the ORDER BY command does; I just dont know how to manipulate the data returned from the query to keep the data in the order I need it to be in.
Below is the SQL String:
SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000714'
UNION
SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000713'
UNION
SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000715'
ORDER BY "CIF Number"
As you can see The "CIF Numbers" are out of order, which is the way they need to be and with the ORDER BY the data is ordered by the CIF#, but I need the data to actually be out of order.
Below is how the current string returns the data:
T000713 | ROGER RABBITT
T000714 | JESSICA RABBIT
T000715 | JAMES KIRK
Below is how I need the data returned:
T000714 | JESSICA RABBITT
T000713 | ROGER RABBITT
T000715 | JAMES KIRK
When I remove the ORDER BY the result returned is this:
T000714 | JAMES KIRK
T000713 | JESSICA RABBITT
T000715 | ROGER RABBITT
sql db2
asked Mar 8 at 17:28
Zack EZack E
394115
add a comment |
I have a scenario that I dont know how to get the correct results due to my lack of knowledge with SQL and need assistance.
I understand what the ORDER BY command does; I just dont know how to manipulate the data returned from the query to keep the data in the order I need it to be in.
Below is the SQL String:
SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000714'
UNION
SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000713'
UNION
SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000715'
ORDER BY "CIF Number"
As you can see The "CIF Numbers" are out of order, which is the way they need to be and with the ORDER BY the data is ordered by the CIF#, but I need the data to actually be out of order.
Below is how the current string returns the data:
T000713 | ROGER RABBITT
T000714 | JESSICA RABBIT
T000715 | JAMES KIRK
Below is how I need the data returned:
T000714 | JESSICA RABBITT
T000713 | ROGER RABBITT
T000715 | JAMES KIRK
When I remove the ORDER BY the result returned is this:
T000714 | JAMES KIRK
T000713 | JESSICA RABBITT
T000715 | ROGER RABBITT
sql db2
asked Mar 8 at 17:28
Zack EZack E
394115
1
The name/number combinations change without theORDER BY? That seems unlikely.
– Eric Brandt
Mar 8 at 17:34
add a comment |
I have a scenario that I dont know how to get the correct results due to my lack of knowledge with SQL and need assistance.
I understand what the ORDER BY command does; I just dont know how to manipulate the data returned from the query to keep the data in the order I need it to be in.
Below is the SQL String:
SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000714'
UNION
SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000713'
UNION
SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000715'
ORDER BY "CIF Number"
As you can see The "CIF Numbers" are out of order, which is the way they need to be and with the ORDER BY the data is ordered by the CIF#, but I need the data to actually be out of order.
Below is how the current string returns the data:
T000713 | ROGER RABBITT
T000714 | JESSICA RABBIT
T000715 | JAMES KIRK
Below is how I need the data returned:
T000714 | JESSICA RABBITT
T000713 | ROGER RABBITT
T000715 | JAMES KIRK
When I remove the ORDER BY the result returned is this:
T000714 | JAMES KIRK
T000713 | JESSICA RABBITT
T000715 | ROGER RABBITT
sql db2
asked Mar 8 at 17:28
Zack EZack E
394115
I have a scenario that I dont know how to get the correct results due to my lack of knowledge with SQL and need assistance.
I understand what the ORDER BY command does; I just dont know how to manipulate the data returned from the query to keep the data in the order I need it to be in.
Below is the SQL String:
SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000714'
UNION
SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000713'
UNION
SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000715'
ORDER BY "CIF Number"
As you can see The "CIF Numbers" are out of order, which is the way they need to be and with the ORDER BY the data is ordered by the CIF#, but I need the data to actually be out of order.
Below is how the current string returns the data:
T000713 | ROGER RABBITT
T000714 | JESSICA RABBIT
T000715 | JAMES KIRK
Below is how I need the data returned:
T000714 | JESSICA RABBITT
T000713 | ROGER RABBITT
T000715 | JAMES KIRK
When I remove the ORDER BY the result returned is this:
T000714 | JAMES KIRK
T000713 | JESSICA RABBITT
T000715 | ROGER RABBITT
sql db2
sql db2
asked Mar 8 at 17:28
Zack EZack E
394115
asked Mar 8 at 17:28
Zack EZack E
394115
asked Mar 8 at 17:28
Zack EZack E
394115
asked Mar 8 at 17:28
Zack EZack E
394115
asked Mar 8 at 17:28
Zack EZack E
394115
394115
1
The name/number combinations change without theORDER BY? That seems unlikely.
– Eric Brandt
Mar 8 at 17:34
add a comment |
1
The name/number combinations change without theORDER BY? That seems unlikely.
– Eric Brandt
Mar 8 at 17:34
1
1
The name/number combinations change without the
ORDER BY? That seems unlikely.– Eric Brandt
Mar 8 at 17:34
The name/number combinations change without the
ORDER BY? That seems unlikely.– Eric Brandt
Mar 8 at 17:34
add a comment |
2 Answers
2
active
oldest
votes
I don't understand why you use UNION. You can do it like this:
SELECT
cfcif# AS "CIF Number",
cfna1 AS "Customer Name"
FROM cncttp08.jhadat842.cfmast cfmast
WHERE cfcif# IN ('T000714', 'T000713', 'T000715')
ORDER BY
CASE cfcif#
WHEN 'T000714' THEN 1
WHEN 'T000713' THEN 2
WHEN 'T000715' THEN 3
END
answered Mar 8 at 17:36
forpasforpas
19.1k3828
Thank you. This is exactly what i was needing and a great learning tool for me to use as I am still really new to SQL and its functionality. Much appreciated!.
– Zack E
Mar 8 at 17:54
add a comment |
Simple!
Remove quotes from the order by clause. It should work. Your code should be like below.
`SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM
cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000714'
UNION
SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM
cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000713'
UNION
SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM
cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000715'
ORDER BY CIF Number`
answered Mar 8 at 18:02
Sathvik ReddySathvik Reddy
265
2
That won't work. If a column name is mixed case, or has a space in it, you will need to put it in double quotes
– Paul Vernon
Mar 8 at 18:47
agreed with your comment.
– Sathvik Reddy
Mar 8 at 22:07
add a comment |
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
);
);
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%2f55068172%2funexpected-result-sql-order-by%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
I don't understand why you use UNION. You can do it like this:
SELECT
cfcif# AS "CIF Number",
cfna1 AS "Customer Name"
FROM cncttp08.jhadat842.cfmast cfmast
WHERE cfcif# IN ('T000714', 'T000713', 'T000715')
ORDER BY
CASE cfcif#
WHEN 'T000714' THEN 1
WHEN 'T000713' THEN 2
WHEN 'T000715' THEN 3
END
answered Mar 8 at 17:36
forpasforpas
19.1k3828
Thank you. This is exactly what i was needing and a great learning tool for me to use as I am still really new to SQL and its functionality. Much appreciated!.
– Zack E
Mar 8 at 17:54
add a comment |
I don't understand why you use UNION. You can do it like this:
SELECT
cfcif# AS "CIF Number",
cfna1 AS "Customer Name"
FROM cncttp08.jhadat842.cfmast cfmast
WHERE cfcif# IN ('T000714', 'T000713', 'T000715')
ORDER BY
CASE cfcif#
WHEN 'T000714' THEN 1
WHEN 'T000713' THEN 2
WHEN 'T000715' THEN 3
END
answered Mar 8 at 17:36
forpasforpas
19.1k3828
Thank you. This is exactly what i was needing and a great learning tool for me to use as I am still really new to SQL and its functionality. Much appreciated!.
– Zack E
Mar 8 at 17:54
add a comment |
I don't understand why you use UNION. You can do it like this:
SELECT
cfcif# AS "CIF Number",
cfna1 AS "Customer Name"
FROM cncttp08.jhadat842.cfmast cfmast
WHERE cfcif# IN ('T000714', 'T000713', 'T000715')
ORDER BY
CASE cfcif#
WHEN 'T000714' THEN 1
WHEN 'T000713' THEN 2
WHEN 'T000715' THEN 3
END
answered Mar 8 at 17:36
forpasforpas
19.1k3828
I don't understand why you use UNION. You can do it like this:
SELECT
cfcif# AS "CIF Number",
cfna1 AS "Customer Name"
FROM cncttp08.jhadat842.cfmast cfmast
WHERE cfcif# IN ('T000714', 'T000713', 'T000715')
ORDER BY
CASE cfcif#
WHEN 'T000714' THEN 1
WHEN 'T000713' THEN 2
WHEN 'T000715' THEN 3
END
answered Mar 8 at 17:36
forpasforpas
19.1k3828
answered Mar 8 at 17:36
forpasforpas
19.1k3828
answered Mar 8 at 17:36
forpasforpas
19.1k3828
answered Mar 8 at 17:36
forpasforpas
19.1k3828
19.1k3828
Thank you. This is exactly what i was needing and a great learning tool for me to use as I am still really new to SQL and its functionality. Much appreciated!.
– Zack E
Mar 8 at 17:54
add a comment |
Thank you. This is exactly what i was needing and a great learning tool for me to use as I am still really new to SQL and its functionality. Much appreciated!.
– Zack E
Mar 8 at 17:54
Thank you. This is exactly what i was needing and a great learning tool for me to use as I am still really new to SQL and its functionality. Much appreciated!.
– Zack E
Mar 8 at 17:54
Thank you. This is exactly what i was needing and a great learning tool for me to use as I am still really new to SQL and its functionality. Much appreciated!.
– Zack E
Mar 8 at 17:54
add a comment |
Simple!
Remove quotes from the order by clause. It should work. Your code should be like below.
`SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM
cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000714'
UNION
SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM
cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000713'
UNION
SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM
cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000715'
ORDER BY CIF Number`
answered Mar 8 at 18:02
Sathvik ReddySathvik Reddy
265
2
That won't work. If a column name is mixed case, or has a space in it, you will need to put it in double quotes
– Paul Vernon
Mar 8 at 18:47
agreed with your comment.
– Sathvik Reddy
Mar 8 at 22:07
add a comment |
Simple!
Remove quotes from the order by clause. It should work. Your code should be like below.
`SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM
cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000714'
UNION
SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM
cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000713'
UNION
SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM
cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000715'
ORDER BY CIF Number`
answered Mar 8 at 18:02
Sathvik ReddySathvik Reddy
265
2
That won't work. If a column name is mixed case, or has a space in it, you will need to put it in double quotes
– Paul Vernon
Mar 8 at 18:47
agreed with your comment.
– Sathvik Reddy
Mar 8 at 22:07
add a comment |
Simple!
Remove quotes from the order by clause. It should work. Your code should be like below.
`SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM
cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000714'
UNION
SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM
cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000713'
UNION
SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM
cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000715'
ORDER BY CIF Number`
answered Mar 8 at 18:02
Sathvik ReddySathvik Reddy
265
Simple!
Remove quotes from the order by clause. It should work. Your code should be like below.
`SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM
cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000714'
UNION
SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM
cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000713'
UNION
SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM
cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000715'
ORDER BY CIF Number`
answered Mar 8 at 18:02
Sathvik ReddySathvik Reddy
265
answered Mar 8 at 18:02
Sathvik ReddySathvik Reddy
265
answered Mar 8 at 18:02
Sathvik ReddySathvik Reddy
265
answered Mar 8 at 18:02
Sathvik ReddySathvik Reddy
265
265
2
That won't work. If a column name is mixed case, or has a space in it, you will need to put it in double quotes
– Paul Vernon
Mar 8 at 18:47
agreed with your comment.
– Sathvik Reddy
Mar 8 at 22:07
add a comment |
2
That won't work. If a column name is mixed case, or has a space in it, you will need to put it in double quotes
– Paul Vernon
Mar 8 at 18:47
agreed with your comment.
– Sathvik Reddy
Mar 8 at 22:07
2
2
That won't work. If a column name is mixed case, or has a space in it, you will need to put it in double quotes
– Paul Vernon
Mar 8 at 18:47
That won't work. If a column name is mixed case, or has a space in it, you will need to put it in double quotes
– Paul Vernon
Mar 8 at 18:47
agreed with your comment.
– Sathvik Reddy
Mar 8 at 22:07
agreed with your comment.
– Sathvik Reddy
Mar 8 at 22:07
add a comment |
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.
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%2f55068172%2funexpected-result-sql-order-by%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
1
The name/number combinations change without the
ORDER BY? That seems unlikely.– Eric Brandt
Mar 8 at 17:34